...

View Full Version : select statement not working



VickP07
10-25-2011, 12:50 AM
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:


<?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>

Old Pedant
10-25-2011, 02:08 AM
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:


$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


$user = $_GET['username'];
...
if( isset($user) && $user != "" )
{
... now you only get here if the value was sent and it is non-blank ...

Old Pedant
10-25-2011, 02:13 AM
How can you not know how to do this when you did almost exactly the same thing just one week ago?

http://www.codingforums.com/showthread.php?t=239661

Did you forget how to use mysql_query and mysql_fetch_array over the weekend? Too much partying?

VickP07
10-25-2011, 04:16 AM
okay so i added this 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?

Old Pedant
10-25-2011, 06:13 AM
DEBUG DEBUG DEBUG



$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,


WHERE ... first_name like '%%'

and indeed that will get you every record in the table.

VickP07
10-25-2011, 06:18 AM
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



<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>

Old Pedant
10-25-2011, 08:42 PM
Up to you. YOu choose which way to do it.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum