mysql_fetch_array and optimization help

[RobNH]

I have built the following chunk of code, but I keep getting the following error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/zruser/public_html/specs/registry.php on line 84

Here is the code:

<?
$mysql_link = mysql_connect("localhost","zruser","mypassword");
			mysql_select_db("mydatabase", $mysql_link);

$query1 = mysql_query("SELECT COUNT(*) FROM registry");
  $count1 = mysql_result($query1, 0);
$query2 = mysql_query("SELECT COUNT(*) FROM registry WHERE wheels='Chrome'");
  $count2 = mysql_result($query2, 0);
$query3 = mysql_query("SELECT COUNT(*) FROM registry WHERE wheels='Painted'");
  $count3 = mysql_result($query3, 0);
$query4 = mysql_query("SELECT COUNT(*) FROM registry WHERE trim_option='1ZR'");
  $count4 = mysql_result($query4, 0);
$query5 = mysql_query("SELECT COUNT(*) FROM registry WHERE trim_option='3ZR'");
  $count5 = mysql_result($query5, 0);
$query6 = mysql_query("SELECT COUNT(*) FROM registry WHERE exterior='Atomic Orange Metallic'");
  $count6 = mysql_result($query6, 0);
$query7 = mysql_query("SELECT COUNT(*) FROM registry WHERE exterior='Black'");
  $count7 = mysql_result($query7, 0);
$query8 = mysql_query("SELECT COUNT(*) FROM registry WHERE exterior='Blade Silver Metallic'");
  $count8 = mysql_result($query8, 0);
$query9 = mysql_query("SELECT COUNT(*) FROM registry WHERE exterior='Cyber Gray Metallic'");
  $count9 = mysql_result($query9, 0);
$query10 = mysql_query("SELECT COUNT(*) FROM registry WHERE exterior='Jetstream Blue Metallic'");
  $count10 = mysql_result($query10, 0);
$query11 = mysql_query("SELECT COUNT(*) FROM registry WHERE exterior='Velocity Yellow'");
  $count11 = mysql_result($query11, 0);
$query12 = mysql_query("SELECT COUNT(*) FROM registry WHERE exterior='Victory Red'");
  $count12 = mysql_result($query12, 0);
$query13 = mysql_query("SELECT COUNT(*) FROM registry WHERE interior='Dark Titanium'");
  $count13 = mysql_result($query13, 0);
$query14 = mysql_query("SELECT COUNT(*) FROM registry WHERE interior='Ebony'");
  $count14 = mysql_result($query14, 0);
$query15 = mysql_query("SELECT COUNT(*) FROM registry WHERE interior='Ebony Red'");
  $count15 = mysql_result($query15, 0);
$query16 = mysql_query("SELECT COUNT(*) FROM registry WHERE interior='Ebony Titanium'");
  $count16 = mysql_result($query16, 0);
$query17 = mysql_query("SELECT COUNT(*) FROM registry WHERE interior='Linen'");
  $count17 = mysql_result($query17, 0);
$query18 = mysql_query("SELECT COUNT(*) FROM registry WHERE interior='Sienna'");
  $count18 = mysql_result($query18, 0);

$query19 = mysql_query ("select max(mileage) as highest_mileage from registry;");
  $count19 = mysql_result($query19, 0);

$results=array();
$sql="select * from registry where mileage=(select max(mileage) from registry) and `id`='".$_REQUEST[id]."' limit 1)";
$result = mysql_query($sql, $mysql_link);
while($a_row = mysql_fetch_array($result, MYSQL_ASSOC)) array_push($results, $a_row);

?>

HTML:

<h3>Registry Statistics:</h3>

<h4>Total 2009 cars Registered:&nbsp;&nbsp; <?print $count1;?></h4>
<h4>Car with the Highest Mileage:&nbsp;&nbsp; <?print "<a href=\"showprofile.php?id=".($results[0][id])."\" target=\"_blank\">".$count19."</a> miles</h4>";?>

<table cellpadding="0" cellspacing="8" border="0" align="center">
  <tr>
    <td valign="top">
<table bgcolor="#000080" border="0" cellspacing="0">
     <tr>
      <td>
<table cellspacing= "1" cellpadding="4" width="100%" bgcolor="#FFFFFF" align="center">
  <tr bgcolor="#BECEE0">
    <th valign="top" align="center" colspan="2">Options:</th>
  </tr>
  <tr>
    <td align="right"><b>Q6A - Painted Wheels:</b></td>
	<td align="right"><input type="text" value="<?print $count3;?>" class="stats" /></td>
  </tr>....etc....

What am I doing wrong and can be php code be simplified or optimized from what it is now?

[laserlight]

There's no line 84 in your sample code and you did not tell us what is the relevant database schema, so it is pretty difficult to say.

What I can say is that when you have such an error message, it means that you should:

• Check the database server connection

• Check the database selection

• Check the relevant SQL statement

[NogDog]

That error usually indicates that MySQL returned an error, being unable to process the query for some reason. I'd suggest adding some debugging along the following lines:

$sql="select * from registry where mileage=(select max(mileage) from registry) and `id`='".$_REQUEST[id]."' limit 1)";
$result = mysql_query($sql, $mysql_link);
if($result == false)
{
   // do something more "graceful" for production version:
   die(mysql_error() . "<br />\n$sql");
}
while($a_row = mysql_fetch_array($result, MYSQL_ASSOC)) array_push($results, $a_row);

[RobNH]

NogDog;10890226 wrote:

That error usually indicates that MySQL returned an error, being unable to process the query for some reason. I'd suggest adding some debugging along the following lines:

$sql="select * from registry where mileage=(select max(mileage) from registry) and `id`='".$_REQUEST[id]."' limit 1)";
$result = mysql_query($sql, $mysql_link);
if($result == false)
{
   // do something more "graceful" for production version:
   die(mysql_error() . "<br />\n$sql");
}
while($a_row = mysql_fetch_array($result, MYSQL_ASSOC)) array_push($results, $a_row);

Here's the error I get:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
select * from registry where mileage=(select max(mileage) from registry) and id='' limit 1)

Also, this problem is not a result of a failed mysql or database connection. The connection is being made and other results are being made on the page.

[laserlight]

Ah. I kind of forgot what is the best syntax to use here, but this should work:

select * from registry where mileage in (select max(mileage) from registry) and `id`='' limit 1)

[RobNH]

laserlight;10890229 wrote:

Ah. I kind of forgot what is the best syntax to use here, but this should work:

select * from registry where mileage in (select max(mileage) from registry) and `id`='' limit 1)

Still getting the same error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
select * from registry where mileage in (select max(mileage) from registry) and id='' limit 1)

:o

[laserlight]

What version of MySQL are you using?

EDIT:
Actually, the subquery seems rather unnecessary. Something like this should suffice:

SELECT * FROM registry WHERE id='' ORDER BY mileage DESC LIMIT 1

[RobNH]

laserlight;10890231 wrote:

What version of MySQL are you using?

5.0.51a-community

[laserlight]

That should support subqueries. Perhaps the syntax problem is due to an extra closing parenthesis at the end. However, the version without a subquery might be even better (or maybe it is worse, I am not a database guru).

[NogDog]

laserlight;10890234 wrote:

That should support subqueries. Perhaps the syntax problem is due to an extra closing parenthesis at the end....

I suspect that wast the issue.

[djjjozsi]

Hi!

i found something, with this, you can download the categories's value.:

<?php

include("connect.php");

$field[]="wheels";
$field[]="trim_option";
$field[]="exterior";
$field[]="interior";

foreach($field AS $r)
{
$command="SELECT registry.$r, Count(registry.$r) AS CountOf$r
FROM registry
GROUP BY registry.$r";
$res=mysql_query($command);
//print $command;
while($rows=mysql_fetch_assoc($res))
{
echo "$r:/". $rows[$r] . "/ ". $rows["CountOf$r"]." element<br>";
$statistic[$rows[$r]]=$rows["CountOf$r"];  //with this value, evry time you write a car, you could print that option's counted value
}
}
// Code generator,http://www.phpcode.hu

?>

[RobNH]

NogDog;10890235 wrote:

I suspect that wast the issue.

I don't know. I don't know what else to look for and I haven't had much luck with getting help on this one.

I just want to be able to show the highest mileage and have that linked, so that the user can click the link and view all of the data in that record with the highest mileage. :o

[laserlight]

I don't know. I don't know what else to look for and I haven't had much luck with getting help on this one.

I think NogDog meant "that was the issue", not "that wasn't the issue".

[RobNH]

djjjozsi;10890239 wrote:

Hi!

i found something, with this, you can download the categories's value.:

<?php

include("connect.php");

$field[]="wheels";
$field[]="trim_option";
$field[]="exterior";
$field[]="interior";

foreach($field AS $r)
{
$command="SELECT registry.$r, Count(registry.$r) AS CountOf$r
FROM registry
GROUP BY registry.$r";
$res=mysql_query($command);
//print $command;
while($rows=mysql_fetch_assoc($res))
{
echo "$r:/". $rows[$r] . "/ ". $rows["CountOf$r"]." element<br>";
$statistic[$rows[$r]]=$rows["CountOf$r"];  //with this value, evry time you write a car, you could print that option's counted value
}
}
// Code generator,http://www.phpcode.hu

?>

I'm not sure how this helps me?

[laserlight]

Have you tried my suggestions yet? Either use my suggestion that does not involve a subquery, or remove the extra parenthesis from the end of your SQL statement.

[djjjozsi]

It gets the highest from the database and makes a link to it.

<?php
include("connect.php");
$sql="SELECT * FROM registry ORDER BY mileage DESC LIMIT 1";
$res=mysql_query($sql);
$max_aged_row=mysql_fetch_object($res);
if(mysql_num_rows($res))
{
print "<hr>";

echo "<b>Max mileAge:</b> $max_aged_row->mileage<br>";
echo "<b>wheels:</b> $max_aged_row->wheels<br>";
echo "<b>trim_option:</b> $max_aged_row->trim_option<br>";
echo "<b>exterior:</b> $max_aged_row->exterior<br>";
echo "<b>interior:</b> $max_aged_row->interior<br>";
echo "<b>mileage:</b> $max_aged_row->mileage<br>";
echo "<a href=\"update.php?id=$max_aged_row->id\">Update</a>";
}

?>

RobNH;10890240 wrote:

I don't know. I don't know what else to look for and I haven't had much luck with getting help on this one.

I just want to be able to show the highest mileage and have that linked, so that the user can click the link and view all of the data in that record with the highest mileage. :o

[RobNH]

laserlight;10890245 wrote:

Have you tried my suggestions yet? Either use my suggestion that does not involve a subquery, or remove the extra parenthesis from the end of your SQL statement.

Actually, I'm mistaken...removing the extra parenthesis did work. So, I'm not getting the error message, but I'm also not getting the id either.

[laserlight]

How should I write it without including a subquery?

I have given you an example, as has djjjozsi.

[djjjozsi]

Let's list the table names into that sql query, start with id ....

RobNH;10890248 wrote:

Actually, I'm mistaken...removing the extra parenthesis did work. So, I'm not getting the error message, but I'm also not getting the id either.

[RobNH]

djjjozsi;10890246 wrote:

It gets the highest from the database and makes a link to it.

<?php
include("connect.php");
$sql="SELECT * FROM registry ORDER BY mileage DESC LIMIT 1";
$res=mysql_query($sql);
$max_aged_row=mysql_fetch_object($res);
if(mysql_num_rows($res))
{
print "<hr>";

echo "<b>Max mileAge:</b> $max_aged_row->mileage<br>";
echo "<b>wheels:</b> $max_aged_row->wheels<br>";
echo "<b>trim_option:</b> $max_aged_row->trim_option<br>";
echo "<b>exterior:</b> $max_aged_row->exterior<br>";
echo "<b>interior:</b> $max_aged_row->interior<br>";
echo "<b>mileage:</b> $max_aged_row->mileage<br>";
echo "<a href=\"update.php?id=$max_aged_row->id\">Update</a>";
}

?>

'

I understand now. This worked! A thousand "thank you's"!!!!!!