[RESOLVED] Get Number From MySQL Database

[The Little Guy]

I want to count the number of items in a database, but I want it to be counted by email. I want it to return a number, so I can compair that number to 5.

$sql = "SELECT * FROM polls COUNT(email) WHERE email='$_SESSION[email]'";
$result = mysql_query($sql);
$row = @mysql_fetch_array($result);
$em = $row["email"];

[Sheldon]

$sql = "SELECT email FROM pools WHERE email = '" . $_SESSION[email] . "' ";
$sql = mysql_query($sql);

$number = num_affected_rows($sql);

[The Little Guy]

num_affected_rows

That's not a fuction, it doesn't work on my server, and I cant find it at PHP.net

[NogDog]

$sql = "SELECT COUNT(*) FROM polls WHERE email='{$_SESSION['email']}'";
$result = mysql_query($sql) or die(mysql_error());
list($count) = mysql_fetch_array($sql);
echo "<p>$count matches on email address {$_SESSION['email']}</p>\n";

[Sheldon]

sorry, mysql_affected_rows http://php.net/mysql_affected_rows

[NogDog]

sorry, mysql_affected_rows http://php.net/mysql_affected_rows
Actually, for a select query you'd use mysql_num_rows().

[The Little Guy]

actually I just used this:

$sql = "SELECT email FROM polls WHERE email='$_SESSION[email]'";
$result = mysql_query($sql);
$number = mysql_num_rows($result);

[Sheldon]

Actually, for a select query you'd use mysql_num_rows().



Thanks Nog Dog!

[Sheldon]

As a follow on from this
I am using this query in an array/for();


$sql[$i] = "SELECT * FROM gm_products WHERE ID = '".$id[$i]."' ";
$sql[$i] = mysql_query($sql[$i]);
echo(@mysql_error());
//echo($sql[$i]);
$num[$i] = mysql_num_rows();

if($num[$i] <= 0)
{
echo("There are no extras for this product.");
}else{
echo("you are doing something wrong sheldon");
}

while($row[$i] = mysql_fetch_assoc($sql[$i]))
{
and am getting this error when i know that there are extra's there to be displayed.

140 <!-- this is the id -->
Warning: Wrong parameter count for mysql_num_rows() in /hosting/asktom/addextras.php on line 145 <!-- what is this? -->
There are no extras for this product. <!-- this should not be showen -->



Any ideas?

[Sheldon]

In case you want to see the whole loop here it is

<?php

$bgcolor = "#D2FED4";

$x = 1;
for ($i=1; $i<7;$i++) {
if ($_POST["product$i"] != "no") {

?>

<tr bgcolor="<?php echo(swap($bgcolor)); ?>" style="padding-top: 3px; padding-bottom: 2px;">

<td valign="top"><?php echo($x++); ?></td>

<td valign="top">

<?php echo(@$product[$i]); ?>
<input type="hidden" name="product<?php echo($i); ?>" value="<?php echo($product[$i]); ?>">

</td>
<td align="left">




<?php
echo($id[$i]);
$sql[$i] = "SELECT * FROM gm_products WHERE ID = '".$id[$i]."' ";
$sql[$i] = mysql_query($sql[$i]);
echo(@mysql_error());
//echo($sql[$i]);
$num[$i] = mysql_num_rows();

if($num[$i] <= 0)
{
echo("There are no extras for this product.");
}else{
echo("you are doing something wrong sheldon");
}

while($row[$i] = mysql_fetch_assoc($sql[$i]))
{
//echo extra items to be added

If($row[$i]['polished_edges'] == "yes")
{
echo($row[$i]['polished_edges']."<br>");
$adda[$i] = $row[$i]['polished_edges_price'];
$adda[$i] = ($circumfrence[$i] * $adda[$i]);
}

If($row[$i]['bevelled'] == "yes")
{
echo($row[$i]['bevelled']."<br>");
$addb[$i] = $row[$i]['bevelled_price'];
$addb[$i] = ($circumfrence[$i] * $addb[$i]);
}

If($row[$i]['holes'] == "yes")
{
echo($row[$i]['holes']."<br>");
$addc[$i] = $row[$i]['holes_price'];
$addc[$i] = ($addc[$i] * 4);
}

if($row[$i]['painting'] == "yes")
{
echo($row[$i]['painting']."<br>");
$addd[$i] = $row[$i]['painting_price'];
$addd[$i] = ($size[$i] * $addd[$i]);
}

if($row[$i]['powerpoints'] == "yes")
{
echo($row[$i]['powerpoints']."<br>");
$adde[$i] = $row[$i]['powerpoints_price'];
}

if($row[$i]['sandblasting'] == "yes")
{
echo($row[$i]['sandblasting']."<br>");
$addf[$i] = $row[$i]['sandblasting_price'];
$addf[$i] = ($size[$i] * $addf[$i]);
}

if($row[$i]['corners'] == "yes")
{
echo($row[$i]['corners']."<br>");
$addg[$i] = $row[$i]['corners_price'];
}

?>
</td>
<td>
<?php
//echo prices to be added to
if($row[$i]['polished_edges'] == "yes")
{
echo($adda[$i]."<br>");
}

if($row[$i]['bevelled'] == "yes")
{
echo($addb[$i]."<br>");
}

if($row[$i]['holes'] == "yes")
{
echo($addc[$i]."<br>");
}

if($row[$i]['painting'] == "yes")
{
echo($addd[$i]."<br>");
}

if($row[$i]['powerpoints'] == "yes")
{
echo($adde[$i]."<br>");
}

if($row[$i]['sandblasting'] == "yes")
{
echo($addf[$i]."<br>");
}

if($row[$i]['corners'] == "yes")
{
echo($addg[$i]."<br>");
}


?>
</td>
<td>
<?php
//echo tick box
If($row[$i]['polished_edges'] == "yes")
{
echo("<input type=\"checkbox\" name=\"".$row[$i]['polished_edges']."\">");
}

If($row[$i]['bevelled'] == "yes")
{
echo("<input type=\"checkbox\" name=\"".$row[$i]['bevelled']."\">");
}

If($row[$i]['holes'] == "yes")
{
echo("<input type=\"checkbox\" name=\"".$row[$i]['holes']."\">");
}

if($row[$i]['painting'] == "yes")
{
echo("<input type=\"checkbox\" name=\"".$row[$i]['painting']."\">");
}

if($row[$i]['powerpoints'] == "yes")
{
echo("<input type=\"checkbox\" name=\"".$row[$i]['powerpoints']."\">");
}

if($row[$i]['sandblasting'] == "yes")
{
echo("<input type=\"checkbox\" name=\"".$row[$i]['sandblasting']."\">");
}

if($row[$i]['corners'] == "yes")
{
echo("<input type=\"checkbox\" name=\"".$row[$i]['corners']."\">");
}

?>
</td>
<?php

}
}
}

?>

[The Little Guy]

try this:
$sql[$i] = "SELECT * FROM gm_products WHERE ID = "$id[$i]'";

[Sheldon]

That and and error with your quotes but thanks, I have rerranged the quotes and added braces and still i dont get a result?



Any more ideas?

[NogDog]

mysql_num_rows() requires one parameter: the result ID from your mysql_query().

[NogDog]

actually I just used this:

$sql = "SELECT email FROM polls WHERE email='$_SESSION[email]'";
$result = mysql_query($sql);
$number = mysql_num_rows($result);
This will provide the desired end result. However, should your database evolve to the point where the query matches hundreds or thousands of rows, you'd be saving all those rows to memory (until you release that result or your script ends); whereas a SELECT COUNT(*) FROM table WHERE [where clause] will only return one row, regardless of the number of matches. Just something to keep in mind as your site and database grow.

[Sheldon]

Thanks Nog Dog, worked like a charm :)