SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    1-800-JMULDER JMulder's Avatar
    Join Date
    May 2001
    Location
    The Netherlands
    Posts
    1,745
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Query Conflicts?

    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:

    PHP 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.
    Jeroen Mulder

    w: www.jeroenmulder.com

  2. #2
    Victory shall be mine tubedogg's Avatar
    Join Date
    Mar 2001
    Location
    Medina, OH
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    PHP Code:
    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); 
    Kevin

  3. #3
    1-800-JMULDER JMulder's Avatar
    Join Date
    May 2001
    Location
    The Netherlands
    Posts
    1,745
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Jeroen Mulder

    w: www.jeroenmulder.com

  4. #4
    SitePoint Zealot moshe_be's Avatar
    Join Date
    Dec 2000
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Heya griply, don't think you can hide

    You are missing an else.

    PHP Code:
    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);


  5. #5
    1-800-JMULDER JMulder's Avatar
    Join Date
    May 2001
    Location
    The Netherlands
    Posts
    1,745
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Flushy! Watcha doing here?

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

    PHP Code:
    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 =(
    Jeroen Mulder

    w: www.jeroenmulder.com

  6. #6
    SitePoint Zealot moshe_be's Avatar
    Join Date
    Dec 2000
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The diffrence is if the visitor enter $carid php will still visit the else and overwrite the query with the generic one.

  7. #7
    Victory shall be mine tubedogg's Avatar
    Join Date
    Mar 2001
    Location
    Medina, OH
    Posts
    440
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Plus, this
    PHP Code:
    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 {
    Kevin

  8. #8
    1-800-JMULDER JMulder's Avatar
    Join Date
    May 2001
    Location
    The Netherlands
    Posts
    1,745
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Last edited by JMulder; Sep 10, 2001 at 06:50.
    Jeroen Mulder

    w: www.jeroenmulder.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •