How to loop through mysql results

[Eamonn]

Ok I have about 40 mysql count(*) querys to execute and then perform some basic maths on the results.

 

Here is the query

 

"select count(*) from entries where lcase(smsc) like '%vodafone%' and logdate >='2007-12-01' and logdate <='2007-12-31' and action='Receive' and to_number in (55555,55556,55557,55558,55558) group by to_number;"

 

This gives the following results when executed from command line mysql.

 

+----------+

| count(*) |

+----------+

|      123 |

|      10 |

|    25878 |

|    1101 |

|    1849 |

+----------+

 

What I would like to end up with is a variable with the name of the to_number which contains the value of the result like this:

 

$55555 = 123;

$55556 = 10;

$55557 = 25878; etc etc

 

My guess is it should be some combination of using nested foreach loops or maybe writing a function that an array is looped through. Im still very new to php so I would be very grateful for any help.

[revraz]

Try using a Fetch

 

http://us3.php.net/mysql_fetch_array

[nikefido]

can you make the first character of a variable a number in PHP?

 

also, popular technique for looping through result sets are using a WHILE loop and mysql_fetch_array()

[Eamonn]

I've attempted to use fetch but it only seems to return the first result (123) I cant get any of the other results out :/

[revraz]

You need to do the fetch in a while loop.

 

while ($row = mysql_fetch_array($result))

[nikefido]

while($counter = mysql_fetch_array($result_set) {
    echo $counter['count(*)']
}

 

 

edit: what he said ^^ 

[GingerRobot]

You shouldn't need to do any nesting. Just select the to_number along with the count. You'll also need to use an array, rather than the variables. A variable name cannot start with a number, so we cant have $55555. Try:

 

<?php
$sql = "select count(*),to_number from entries where lcase(smsc) like '%vodafone%' and logdate >='2007-12-01' and logdate <='2007-12-31' and action='Receive' and to_number in (55555,55556,55557,55558,55558) group by to_number;"
$result = mysql_query($sql) or die(mysql_error());
$nums[] = array()
while(list($count,$num) = mysql_fetch_row($result)){
$nums[$num] = $count;
}
echo $nums[55555];
?>

[Eamonn]

Thanks thats half way to what I need to do

 

Instead of echo'ing the result I need to turn it into a variable is this possible?

[kenrbnsn]

One solution:

<?php
$q = "select to_number, count(*) as my_count from entries where lcase(smsc) like '%vodafone%' and logdate >='2007-12-01' and logdate <='2007-12-31' and action='Receive' and to_number in (55555,55556,55557,55558,55558) group by to_number";
$rs = mysql_query($q);
$counts = array();
while ($rw = mysql_fetch_assoc($rs)) {
     $counts[$rw['to_number']] = $rw ['my_count'];
echo '<pre>' . print_r($counts,true) . '</pre>'; // debug
?>

 

Ken

[revraz]

It is a variable...

[GingerRobot]

Did you read what i said? We're using an array, because variable names cannot start with a number.

 

I added an echo at the end as an example

[Eamonn]

Ginger: I tried that code but it doesnt work.. just gives me a blank page. But from reading through it your defo on the right track.

 

I even changed echo $nums[55555]; to echo $nums['55555']; but that didnt work either. Any ideas?

 

Thanks a million to everyone who has posted so far this has been melting my brain for days

[GingerRobot]

Somehow i managed to miss off three semi-colons in that post. I noticed one and modified, but didn't see the other two. I must be tired!

 

Try:

 

<?php
$sql = "select count(*),to_number from entries where lcase(smsc) like '%vodafone%' and logdate >='2007-12-01' and logdate <='2007-12-31' and action='Receive' and to_number in (55555,55556,55557,55558,55558) group by to_number;";
$result = mysql_query($sql) or die(mysql_error());
$nums[] = array();
while(list($count,$num) = mysql_fetch_row($result)){
$nums[$num] = $count;
}
echo $nums[55555];
?>

[Eamonn]

GingerRobot you absolute legend!!!! Thank you so much for helping me I have been so frustrated by this for days.

 

Thanks!!!!!