How to use SQL with PHP

Post Reply
Tony
Lieutenant
Lieutenant
Posts: 86
Joined: Tue Jul 21, 2009 4:11 pm

How to use SQL with PHP

Post by Tony » Sun Nov 29, 2009 5:05 am

This SQL and PHP will count how many rows contain unique elements in your database. For example, you may have a database of user entries and want to know how many entries each user has. In this example, you want to know how many rows contain each unique element "name":

Code: Select all

$query = mysql_query("SELECT name, COUNT(*) AS number FROM $table GROUP BY name");
if ( mysql_num_rows($query) == 0 ) echo "No rows in the database";
while ($row = mysql_fetch_array($query)) {
  $name = $row['name'] ;
  $count = $row['number'] ;
  echo "$name has $count rows in the database<br>";
}  
Use this to take a single table backup, with elements in double quotes delimited with a comma:

Code: Select all

while ($row = mysql_fetch_array($query,MYSQL_NUM)) $output .= "\"" . implode("\",\"",str_replace("\r\n"," ",$row)) . "\"\r\n";
echo $output; // or write $output to a file  
Use this to count the rows that meet a select criteria. You can use any WHERE criteria you wish, or none (as in the example below which counts the total rows).

Code: Select all

$query = mysql_query("SELECT COUNT(*) AS number FROM $table LIMIT 1");
$row = mysql_fetch_array($query);
if ($row['number'] > 1000 ) echo "You have over 1,000 rows"; 
Post Reply

Return to “PHP & MySQL”