Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New to the CF scene
    Join Date
    Aug 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Arrow Need Help making a SQL query

    hey guys i need some help with my sql query. i'm a bit of a rookie with coding so any hlp would be greatly appreciated.

    what im trying to accomplish is...

    i want to retrieve some database info based from an id number (this part i have )
    based on that number i want to get the 'AptNum' value

    PHP Code:
        $data mysql_query("    
                                SELECT a.AptNum
                                FROM appointment a
                                WHERE a.PatNum = '"
    .$search_query."'
                                ORDER BY a.AptDateTime DESC
                                LIMIT 1                                                         
                             "
    ); 

     while(
    $info mysql_fetch_array$data )) 
     { 
      
    $AptNum $info['AptNum'];

    since i only want the very last entry based on the ID number, i have it sorted by a date stamp and limit to 1. **i would like to add an if statement to this something like if ($AptNum != 'null') { $dothis = "blah"; } else { $dothis ="blahblah"; }**.

    Next, i want to use the 'AptNum' value to get more info from a different table. since the rows returned could be multiple from this table and i only want the latest row from the previous table i did two separate queries

    PHP Code:
    $data mysql_query("
        
                                SELECT p.CodeNum,c.AbbrDesc
                                FROM procedurelog p
                                LEFT JOIN procedurecode c ON (p.CodeNum=c.CodeNum)
                                WHERE p.AptNum = '"
    .$AptNum."'
                                ORDER BY p.ProcDate DESC
                                
                            
                                    
                             "
    );
     

     while(
    $info mysql_fetch_array$data )) 
     { 
      
    $Code $info['CodeNum'];
     
     if (
    $Code == "25"
     {
     
    $test "this is a test";
     }
     elseif (
    $Code == "30")
     
    $test "test 2";
     } 
    echo 
    $Code.$test."<br>";

    what i want that query to do is return ALL the results (CodeNum) for that 'AptNum' value. ive managed to get this 2 queries to work they wany i want; however, I want to add some comments/text to each 'CodeNum' that is returned. when i try to add the if statement i run into problems.. when i have the if statement without the else part i get something like this

    Code:
    25this is a test
    30this is a test
    31this is a test
    which clearly isnt what i want.. what i want is something like this

    Code:
    25 this is 25
    30 this is 30
    31this is 31
    and for some reason when i add the else or elseif to my if statement i get an error
    Code:
    Parse error: syntax error, unexpected 'this' (T_STRING) in C:\xampp\htdocs\WFD\ugage\test.php on line 72
    i hope i was able to explain my situation weel enough for someone to be able to offer some help.

    thank for the help and your time

    cheers

  • #2
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,530
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Ironically, before you asked a MySQL question in the PHP forum, and now a PHP question in the MySQL forum...

    To the first part: you don't need a while loop if you know you're only getting back one row.

    PHP Code:
    $info mysql_fetch_array$data ); 
    works fine. Now what's wrong with your theoretical if statement? What's preventing you from doing it? If your database column allows null, you don't test against 'null'... that's testing against the literal string 'null', the word. You just put NULL with no quotes.

    As for the second question, you clearly removed some code, since there is no problematic this in there. However

    PHP Code:
    if ($Code == "25"
     {
     
    $test "this is a test";
     }
     elseif (
    $Code == "30")
     
    $test "test 2";
     } 
    After your elseif, you have no opening bracket, but you have a closing one. This will cause issues; fix it.

  • #3
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,958
    Thanks
    120
    Thanked 76 Times in 76 Posts
    As far as I see php uses ' instead of " (as you do) for fixed strings, fix that first.
    http://php.net/manual/en/language.types.string.php

    EDIT:

    and, ahh, yes:

    if ($Code == "25")
    {
    $test = "this is a test";
    }
    elseif ($Code == "30")
    {
    $test = "test 2";
    }
    Last edited by BubikolRamios; 08-27-2013 at 08:55 PM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #4
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,530
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Quote Originally Posted by BubikolRamios View Post
    As far as I see php uses ' instead of " (as you do) for fixed strings, fix that first.
    You can use either or. There is a slight speed difference, but unless you have a massive string, it doesn't matter. The only difference is that single quote is a literal string, eg, won't process variables or escaped characters.

  • #5
    New to the CF scene
    Join Date
    Aug 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    okay so it seem i just forgot a to add a " and a {..everythnig seems to work okay now..except for 1 thing i need to add a condition statement that states
    if $search_query value isnt in the table then return an error msg else proceed as normal.. how would i go about doing that? and also, is there a better way to wirte my code? thanks for the help guys


    PHP Code:
     <?php
    $search_query 
    mysql_real_escape_string($_POST['patnumber']);

     
    mysql_connect("****""***""") or die(mysql_error()); 
     
    mysql_select_db("*****") or die(mysql_error()); 
     
        
    $data mysql_query("    
                                SELECT a.AptNum
                                FROM appointment a
                                WHERE a.PatNum = '"
    .$search_query."'
                                ORDER BY a.AptDateTime DESC
                                LIMIT 1                                             
                             "
    ); 

    $info mysql_fetch_array$data ); 
    $AptNum $info['AptNum'];
    $data mysql_query("    
                                SELECT p.CodeNum,c.AbbrDesc
                                FROM procedurelog p
                                LEFT JOIN procedurecode c ON (p.CodeNum=c.CodeNum)
                                WHERE p.AptNum = '"
    .$AptNum."'
                                ORDER BY p.ProcDate DESC
                                    
                             "
    ); 

     while(
    $info mysql_fetch_array$data )) 
     { 
      
    $Code $info['CodeNum'];
     
     if (
    $Code == "25"
     {
     
    $test "this is 25";
     }
     elseif (
    $Code == "30")
     {
     
    $test "this is 30";
     }
       elseif (
    $Code == "31")
     {
     
    $test "this is 31";
     }
    else
    {
    $test "no code#";
    }
     
    echo 
    $Code."&nbsp".$test."<br>";

    ?>

  • #6
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,530
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Are you trying to find out if your first query returns no rows, or if the returned value is NULL?

  • #7
    New to the CF scene
    Join Date
    Aug 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Keleth View Post
    Are you trying to find out if your first query returns no rows, or if the returned value is NULL?
    i think it returns no rows...

  • #8
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,530
    Thanks
    45
    Thanked 259 Times in 256 Posts
    PHP Code:
     if (mysql_num_rows($data)) {
    // do stuff if there are rows
    } else {
    // show error

    But really, you should move to mysqli or PDO, since the stand-alone functions will be deprecated soon enough.

  • #9
    New to the CF scene
    Join Date
    Aug 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Keleth View Post
    PHP Code:
     if (mysql_num_rows($data)) {
    // do stuff if there are rows
    } else {
    // show error

    But really, you should move to mysqli or PDO, since the stand-alone functions will be deprecated soon enough.
    thank you for your help and advice, much appreciated!


  •  

    Posting Permissions

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