[SOLVED]SQL problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Milan
Forum Commoner
Posts: 97
Joined: Wed May 17, 2006 6:08 pm

[SOLVED]SQL problem

Post by Milan »

Go to http://alpha.pickageek.com/searchx.php and try searching for username Milan ; interest = PHP

you will get

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/content/p/i/c/pickageek/html/alpha/searchx.php on line 52

What is going on in there???

this is the page code

Code: Select all

<?php
print <<<_HTML_
<BODY>
	<h1>	  Pickageek DB Search<hr></h1>
		<form  method="post" action="">	
		<table width="396" border="0">
		  <tr>
			<td width="80"><div align="left">User name</div></td>
			<td width="306"><input name="user_name" type="text" value = "$_POST[user_name]" size="40"></td>
		  </tr>
		  <tr>
			<td><div align="left">Interest</div></td>
			<td><input name="interest" type="text" value = "$_POST[interest]" size="40"></td>
		  </tr>
		  <tr>
			<td><div align="left">Rating</div></td>
			<td><input type="text" name="rating" value = "$_POST[rating]"></td>
		  </tr>
		  <tr>
			<td><div align="left">Completed</div></td>
			<td><input type="text" name="completed" value = "$_POST[completed]" ></td>
		  </tr>
		  <tr>
			<td height="45"><input type="submit" name="Submit" value="Search"></td>
			<td>&nbsp;</td>
		  </tr>
		</table>
		</form>
</BODY>
_HTML_;

if ($_POST['Submit']){
	//if submit button is pressed
	$where = '';
	if($_POST['user_name']  != '')
		$where .= (($where=='') ? 'where ' : ' AND ') . "username like '" . str_replace("*" ,"%",$_POST['user_name'] . "*") . "'";
	if($_POST['interest']  != '')
		$where .= (($where=='') ? 'where ' : ' AND ') ."((expertise like " . str_replace("*","%",$_POST['interest']) . ") OR (resume like " . str_replace("*","%",$_POST['interest']) . "))";
	if($_POST['rating']  != '')
		$where .= (($where=='') ? 'where ' : ' AND ') . "(rating >= " . $_POST['rating'] . ")";
	if($_POST['completed']  != '')
		$where .= (($where=='') ? 'where ' : ' AND ') . "(Comprojects = " . $_POST['completed'] . ")";	


	//blank where means no search is selected so no need to perform any DB operation
	if ($where !=''){
		$link = mysql_connect('mysql127.secureserver.net','root','pass') or die('Database connection Error!!');
		mysql_select_db('pickageek');
		$query = "Select username,rating,points,expertise,resume from users " . $where ;
		print "<B>Query string: <br>" . $query  . '<br>Results:<br>';
		$result_set = mysql_query($query);
		while ($row = mysql_fetch_assoc($result_set)) {
		   print $row['username'] . 
				" rated: " . $row['rating'] . 
				" points: " . $row['points'] . "<br>";
				}
		mysql_free_result($result_set);
		mysql_close($link);
	}
}

?>
Last edited by Milan on Sun Jun 04, 2006 2:50 am, edited 1 time in total.
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post by bdlang »

Try changing these lines:

Code: Select all

mysql_select_db('pickageek') OR die('DB SELECT ERROR:<br />'.mysql_error());

// and
$result_set= mysql_query($query) OR die('SQL ERROR:<br />'.mysql_error());
See if you can trap the error on one of those calls.
Milan
Forum Commoner
Posts: 97
Joined: Wed May 17, 2006 6:08 pm

Post by Milan »

i got this

SQL ERROR:
Unknown column 'php' in 'where clause'
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post by bdlang »

Ok, so you need to wrap the values coming from $_POST in quotes within your $where string. MySQL is treating them as column names because they're unquoted.
Milan
Forum Commoner
Posts: 97
Joined: Wed May 17, 2006 6:08 pm

Post by Milan »

You people are my ambasadors of coolness!

THANKS ONCE AGAIN!
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

A little error checking in this code would be good:

Code: Select all

$query = "Select username,rating,points,expertise,resume from users " . $where ;
                print "<B>Query string: <br>" . $query  . '<br>Results:<br>';
                $result_set = mysql_query($query);
                if ($errmsg = mysql_error()) {
                         print "$errmsg<br>";
                } else {
                    while ($row = mysql_fetch_assoc($result_set)) {
                         print $row['username'] .
                                " rated: " . $row['rating'] .
                                " points: " . $row['points'] . "<br>";
                                }
                }
(#10850)
Post Reply