Need help slightly modifying this SQL query.

[MoreBloodWine]

Ok, so the winning query and php / html works the way I expect it to but I'd like to add one small modification hence me seeking help here.

Right now it might return a result such as this...

123
123
456
123
789
342
123
456

Well, while that's all fine n dandy it can make for a long table. What I'd like to do is "compress" this info.

So right now $row['winning_address'] prints out what you would see in the example above. I'd like the query modified so the result would look as below. The new "value" would be placed where the tbc... is in the other td of the below table.

123 ---- 4
456 ---- 2
342 ---- 1
789 ---- 1

So in the table below using the new query, the first TD wold spit out each unique "address" with the second value in the second TD being how many times each "address" was found.

Ordering doesn't really matter so long as the number of times found is shown in the other td to the correct corresponding "address".

Ty.

Code Select Expand

$curr_players = mysql_query("SELECT * FROM entries");

// Begin FOREACH's//
if($row != ''){
while($row = mysql_fetch_array($curr_players)) {
echo '
<tr align="center">
<td>
&nbsp;'.$row['winning_address'].'&nbsp;
</td>

<td>
&nbsp;tbc...&nbsp;
</td>
</tr>
';
}
}else{
echo '
<tr align="center">
<td colspan="2">
No data found / connection error.
</td>
';

}
// End FOREACH's //
[code]

[Arantor]

Impossible to help you without knowing what columns are in the entries table since the correct course of action is a GROUP BY clause.

[MoreBloodWine]

Impossible to help you without knowing what columns are in the entries table since the correct course of action is a GROUP BY clause.

winning_address

[Arantor]

Um, I need more details about how this table is constructed because what my change would be... would likely be expensive computationally.

[MoreBloodWine]

Um, I need more details about how this table is constructed because what my change would be... would likely be expensive computationally.

The tables wiped weekly so it would never grow too large. Probably never more than 100, 125 entries, and those would be really good weeks, depending on use. As for the tables construction. Other than an the winners_address there's just an id field which is incrementally set and reset upon wipe.

Beyond that I'm not too sure on what your asking I'm afraid.

Ty.

Ps; I don't know / doubt it'll help but here's an image.

[Arantor]

Ugh, there's really no way to make this even remotely efficient if it is as described.

Code Select Expand

$curr_players = mysql_query("SELECT winning_address, COUNT(winning_address) AS count FROM entries GROUP BY winning_address ORDER BY count DESC");

// Begin FOREACH's//
if($row != ''){
while($row = mysql_fetch_assoc($curr_players)) {
echo '
<tr align="center">
<td>
&nbsp;' . $row['winning_address'] . '&nbsp;
</td>

<td>
&nbsp;' . $row['count'] . '&nbsp;
</td>
</tr>
';
}
}else{
echo '
<tr align="center">
<td colspan="2">
No data found / connection error.
</td>
';

}
// End FOREACH's //


No idea what the value of $row is beforehand, but I see no reason why you actually should be testing on that, as opposed to, say, !empty($curr_players)

[MoreBloodWine]

First, ty as always Arantor.

Ugh, there's really no way to make this even remotely efficient if it is as described.

What's the issue, the fact that the tables wiped weekly or just the rows in the table ?

No idea what the value of $row is beforehand, but I see no reason why you actually should be testing on that, as opposed to, say, !empty($curr_players)

The "original" code was something borrowed from an oooold project. I am by no means oposed to it being re-written so it is I supposed one might say constructed properly.

Edit: Here's the contents of the entire file used.

Code Select Expand

<?php

$username="user";
$password="db";
$database="pass";

    mysql_connect('localhost',$username,$password);
    @mysql_select_db($database) or die ("Unable to select database.");

// Query to call participants. //
$curr_players = mysql_query("SELECT * FROM entries");

mysql_close();

echo '
<head>
<title>title</title>

<style type="text/css">
table#users {
border: solid 3px #9E853D;
}
table#users td {
border: solid 1px #9E853D;
}
a{
color:blue
}
</style>
</head>

<body>
<center>
Back to Home: <a href="url">Home</a>
<div>
<table id="users">
<tr align="center">
<td colspan="2">
&nbsp;something&nbsp;
</td>
</tr>
<tr align="center">
<td>
something else
</td>

<td>
&nbsp;something new&nbsp;
</td>
</tr>
';

// Begin FOREACH's//
if($row != ''){
while($row = mysql_fetch_array($curr_players)) {
echo '
<tr align="center">
<td>
&nbsp;'.$row['winning_address'].'&nbsp;
</td>

<td>
&nbsp;tbc...&nbsp;
</td>
</tr>
';
}
}else{
echo '
<tr align="center">
<td colspan="2">
No data found / connection error.
</td>
';

}
// End FOREACH's //

echo '
</table>
</div>
</center>
</body>
';

?>

[Arantor]

What's the issue, the fact that the tables wiped weekly or just the rows in the table ?

Lack of sane indexes.

[MoreBloodWine]

What's the issue, the fact that the tables wiped weekly or just the rows in the table ?

Lack of sane indexes.

Ahh, well ty for the help still. I did update my last reply though to include the files entire set of "code" in case it's needed for whatever reason.

[MoreBloodWine]

No idea what the value of $row is beforehand, but I see no reason why you actually should be testing on that, as opposed to, say, !empty($curr_players)

Ps; As far as this goes. Do you mean just simply using this ?

Code Select Expand


if(!empty($curr_players)){


[Arantor]

That was the general idea, though using mysql_close before the query result is processed is... interesting.

[MoreBloodWine]

That was the general idea, though using mysql_close before the query result is processed is... interesting.

Well like I said, old code. However, since the code was posted. Would you wana maybe clean that up a tad and format it how it should be for optimal performance ?

Even though there's not much to be done with the query due to the sane index thing you mentioned.

Ps; When I posted the code I hadn't added you new query yet.

Ty.

[Arantor]

There's really not that much I can do with the code to be honest. It won't be any faster, it's just interesting to see a structure that runs counter to every piece of advice I've ever seen on the subject.

[MoreBloodWine]

There's really not that much I can do with the code to be honest. It won't be any faster, it's just interesting to see a structure that runs counter to every piece of advice I've ever seen on the subject.

So  really the only changes would be just the empty thing then and say the sql close after the foreach / if statement ?

[Arantor]

Pretty much, yeah.

[MoreBloodWine]

Pretty much, yeah.

Well as always, Ty.

I know we have butted heads in the past due to my stubbornness sometimes but I value everything you have to say.

[MoreBloodWine]

Hey Arantor, I somehow managed to overlook this but if there's no data to return. The else statement doesn't seem to show.

Code Select Expand

// Begin FOREACH's//
if(!empty($curr_players)){
while($row = mysql_fetch_array($curr_players)) {
echo '
<tr align="center">
<td>
&nbsp;<a href="https://blockchain.info/address/'.$row['winning_address'].'">'.$row['winning_address'].'</a>&nbsp;
</td>

<td>
&nbsp;'.$row['count'].'&nbsp;
</td>
</tr>
';
}
}else{
echo '
<tr align="center">
<td colspan="2">
No data found / connection error.
</td>
</tr>
';

}
// End FOREACH's //


I did test it with populated info and it tallies / works fine, it's just the else statement not working when there's nothing to return.

Ty.

Edit: I know you had a concern with $row so please feel free to re-write to where you think it should work ok.

Ty.

[Arantor]

You realise I removed it using mysql_fetch_array and replaced it with mysql_fetch_assoc once already right? That was intentional.

Code Select Expand

// Begin FOREACH's//
if(!empty($curr_players) && mysql_num_rows($curr_players) > 0){
while($row = mysql_fetch_assoc($curr_players)) {
echo '
<tr align="center">
<td>
&nbsp;<a href="https://blockchain.info/address/'.$row['winning_address'].'">'.$row['winning_address'].'</a>&nbsp;
</td>

<td>
&nbsp;'.$row['count'].'&nbsp;
</td>
</tr>
';
}
}else{
echo '
<tr align="center">
<td colspan="2">
No data found / connection error.
</td>
</tr>
';

}
// End FOREACH's //


[MoreBloodWine]

You realise I removed it using mysql_fetch_array and replaced it with mysql_fetch_assoc once already right? That was intentional.

Code Select Expand

// Begin FOREACH's//
if(!empty($curr_players) && mysql_num_rows($curr_players) > 0){
while($row = mysql_fetch_assoc($curr_players)) {
echo '
<tr align="center">
<td>
&nbsp;<a href="https://blockchain.info/address/'.$row['winning_address'].'">'.$row['winning_address'].'</a>&nbsp;
</td>

<td>
&nbsp;'.$row['count'].'&nbsp;
</td>
</tr>
';
}
}else{
echo '
<tr align="center">
<td colspan="2">
No data found / connection error.
</td>
</tr>
';

}
// End FOREACH's //


My apologies, I don't know how the "original" array code got back in, ty for pointing that out but tested what you just posted and returns the else statement just fine, Ty.