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 7 of 7
  1. #1
    New Coder
    Join Date
    Sep 2011
    Posts
    78
    Thanks
    15
    Thanked 0 Times in 0 Posts

    select statement not working

    Hey guys so i am trying to write is a PHP web service page to search for users. It should respond to HTTP GET
    requests and take a string to search for as a query string variable. It should return the username, first_name and
    last_name of all users who have that search string as part of their username, first_name or last_name.

    So right now I open my page:
    paguilary/test/find-user2.php

    and then at the end of the string i add ?username=et

    I should get "etomai" and also any other user with "et" anywhere, in their username, first name or last name.

    Right now i am just testing to see if the users that end in whatever i type at the end of the string will work....but i am BEYOND LOST as to how to display all the users that might have 'et' anywhere in their username.....

    Right now in my if statement my sql query is correct its the displaying part taht I dont know how to do.



    here is my code:
    Code:
    <?php
    
    $db = mysql_connect( "localhost","root", "temp1234");
    mysql_select_db( "csci3342");
    
    $user = $_GET['username'];
    $firstname = $_GET['first_name'];
    
    // start xml document
    header( "Content-type: text/xml" );
    print "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";
    
    ?>
    
    <value>
    <?
    if( $user )
    {
    $sql = "
    SELECT username FROM users
    WHERE username like '%$user%';
    	  ";
    
    	
    	echo "<UserName>" . $sql. "</UserName>\n";
    
    } else if( $firstname ) {
    
    	echo "<FirstName>" . $firstname . "</FirstName>\n";
    
    }
    ?>
    </value>

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    You have to actually *USE* the query and then you have to LOOP through the results.

    Look at example 2 on this page:
    http://www.php.net/manual/en/function.mysql-query.php

    But another problem you have is here:
    Code:
    $user = $_GET['username'];
    ...
    if( $user )
    {
         ... YOUR CODE WILL *ALWAYS* GET TO HERE! ...
    Assuming that there is a username <form> field on the page that targeted this one, then that $_GET will *always* get a value. It might be a blank string ( "" ) but it won't be empty.

    So you should use
    Code:
    $user = $_GET['username'];
    ...
    if( isset($user) && $user != "" )
    {
         ... now you only get here if the value was sent and it is non-blank ...
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    VickP07 (10-25-2011)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    How can you not know how to do this when you did almost exactly the same thing just one week ago?

    IF Statement (select statement)

    Did you forget how to use mysql_query and mysql_fetch_array over the weekend? Too much partying?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New Coder
    Join Date
    Sep 2011
    Posts
    78
    Thanks
    15
    Thanked 0 Times in 0 Posts
    okay so i added this code:
    Code:
    <value>
    <?php
    if( $user )
    {
    $sql = "
    SELECT username, first_name, last_name FROM users
    WHERE username like '%$user%'
    	OR first_name like '%$firstname%';
    	  ";
    	#test if select statement works
    	$result2 = mysql_query( $sql );
    	$result2 or die("My query ($sql) failed." );
    	
     while( $row = mysql_fetch_array( $result2 ) ) 
     { 
      echo "<Username>" . $row['username'] . "</Username>\n";
      echo "<FirstName>" . $row['first_name'] . "</FirstName>\n";
    
     } 
    	//echo "<FirstName>" . $result2 . "</FirstName>\n";
    
    } else if( $firstname ) {
    
    }
    ?>
    </value>
    If i take off the OR first_name like line and just search under username it works PERFECT!!! it gives me all users that have for example 'et' in the username. but when i try and do this code that i posted it just gives me all usernames and firstnames in the database.

    What am i dng wrong?

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    DEBUG DEBUG DEBUG

    Code:
    $sql = "
    SELECT username, first_name, last_name FROM users
    WHERE username like '%$user%'
    	OR first_name like '%$firstname%';
    	  ";
    
    echo "<hr>DEBUG sql: " . $sql . "<hr/>";
    ...
    What does that show you?

    I am betting the $firstname is a blank string, so you are doing, essentially,
    Code:
         WHERE ... first_name like '%%'
    and indeed that will get you every record in the table.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    New Coder
    Join Date
    Sep 2011
    Posts
    78
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Okay i changed my code since the last time i visited the page
    Now after my link i add ?username='et'&first_name=et&last_name=et

    and i get the correct data from the DB displayed on the page. But, i dont think this is
    what i am supposed to be doing. I think what i am supposed to be dng (HW assignment)
    is only enter username='et' and then display the firstnames, and lastnames that also contain 'et' inside them as well

    Code:
    <value>
    <?php
    if( ($user ) && ($firstname) && ($lastname))
    {
    $sql = "
    SELECT username, first_name, last_name FROM users
    WHERE username like '%$user%'
    	or first_name like '%$firstname%'
    	or last_name like '%$lastname%';
    	  ";
    	#test if select statement works
    	$result2 = mysql_query( $sql );
    	$result2 or die("My query ($sql) failed." );
    	
     while( $row = mysql_fetch_array( $result2 ) ) 
     { 
      echo "<Username>" . $row['username'] . "</Username>\n";
      echo "<FirstName>" . $row['first_name'] . "</FirstName>\n";
      echo "<LastName>" . $row['last_name'] . "</LastName>\n";
     } 
    	//echo "<FirstName>" . $result2 . "</FirstName>\n";
    
    } else if( $firstname ) {
    
    
    }
    ?>
    </value>

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Up to you. YOu choose which way to do it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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