MySQL Query Conflicts?

[JMulder]

Well, I am using a script that gets information from a database, just like a normal thing

I am using IF-statements to define what needs to be queried, here's an excerpt of the code:

settype($carid,"integer");
if ($carid != ""){
  $CarList = mysql_query("SELECT * FROM Cars WHERE CarID = '$carid'");
}

settype($author,"string");
if ($author != ""){
  $CarList = mysql_query("SELECT * FROM Cars WHERE AuthorA = '$author'");
}
else {
$CarList = mysql_query("SELECT * FROM Cars order by $sortby $sortbyOrder LIMIT $startpoint,5");
$CarListNext = mysql_query("SELECT * FROM Cars order by $sortby $sortbyOrder LIMIT $startpointNext,5");
$totalNext = mysql_num_rows($CarListNext);
} 

Now this is the part where it goes wrong, I recently added the $carid string and it worked perfectly without the ' around the $carid in its MySQL Query. Now I added the $author string and did the exact same thing as with the $carid one.

Now it didnt work, so I added the ' around the $author ONLY and left the $carid as it was, it worked! But now the $carid string stopped working, so I thought I'd add ' around the $carid in the query as well, it still didnt work, only the $author string does =|

It seems it just overlooks the $carid string when I have added the ' around the $author variable in the query =|

Thanks in advance.

[tubedogg]

Actually the problem is you are trying to assign two entirely separate queries to the same variable, and ending up overwriting the results of the first with the results of the second.

Try this:

settype($carid,"integer");
settype($author,"string");
// determine what's got a value and what doesn't, and set the WHERE clause accordingly
if ($carid!="" && $author!=""){
  $where="WHERE CarID=$carid AND AuthorA='$author'";
} elseif ($carid=="" && $author!="") {
  $where="WHERE AuthorA='$author'";
} elseif ($carid!="" && $author=="") {
  $where="WHERE CarID=$carid";
} else {
  // neither $carid nor $author is set
  $where="";
}

// run the queries
$CarList = mysql_query("SELECT * FROM cars $where ORDER BY $sortby $sortbyorder LIMIT $startpoint,5");
$CarListNext = mysql_query("SELECT * FROM cars $where ORDER BY $sortby $sortbyorder LIMIT $startpintNext,5");
$totalNext = mysql_num_rows($CarListNext); 

[JMulder]

Well, I forgot to mention I won't use both variables in one URL =|

So then it wouldn't overwrite it right?

I will have URL's like:

...?carid=34 //Shows Car #34
...?author=Whacko //Shows all Cars made by Wacko

[moshe_be]

Heya griply, don't think you can hide

You are missing an else.

settype($carid,"integer");
settype($author,"string");

if ($carid != ""){
  $CarList = mysql_query("SELECT * FROM Cars WHERE CarID = '$carid'");
} elseif ($author != ""){
  $CarList = mysql_query("SELECT * FROM Cars WHERE AuthorA = '$author'");
} else {
   $CarList = mysql_query("SELECT * FROM Cars order by $sortby $sortbyOrder LIMIT $startpoint,5");
   $CarListNext = mysql_query("SELECT * FROM Cars order by $sortby $sortbyOrder LIMIT $startpointNext,5");
   $totalNext = mysql_num_rows($CarListNext);
} 

[JMulder]

Flushy! Watcha doing here?

Welp, I tried that already only in a different combination:

settype($carid,"integer");
if ($carid != ""){
  $CarList = mysql_query("SELECT * FROM Cars WHERE CarID = '$carid'");
}

settype($author,"string");
elseif ($author != ""){
  $CarList = mysql_query("SELECT * FROM Cars WHERE AuthorA = '$author'");
}
else {
$CarList = mysql_query("SELECT * FROM Cars order by $sortby $sortbyOrder LIMIT $startpoint,5");
$CarListNext = mysql_query("SELECT * FROM Cars order by $sortby $sortbyOrder LIMIT $startpointNext,5");
$totalNext = mysql_num_rows($CarListNext);
} 

Which shouldn't make any difference right? Ah well, I'll try the way you did it Flushy ..whenever Messiah gets back and resets the server =(

[moshe_be]

The diffrence is if the visitor enter $carid php will still visit the else and overwrite the query with the generic one.

[tubedogg]

Plus, this

if ($carid != ""){
  $CarList = mysql_query("SELECT * FROM Cars WHERE CarID = '$carid'");
}

settype($author,"string");
elseif ($author != ""){
  $CarList = mysql_query("SELECT * FROM Cars WHERE AuthorA = '$author'");
} 

is not valid. You can't put anything between the } and the elseif {

[JMulder]

Aaaah!! That's it, I never read I couldn't put anything in between although it does make sense

Server is back, going to try it now, thanks guys

EDIT: Yeee! It worked