[Resolved] simple sql query using 1 variable

jollyollie

I'm trying to create a search function using a userdefined variable, but can't get it to work. I've tried a number of things, but it gives me errors. I can put in an echo statement that returns the variable, so I know that the $countyname goes through. I can also hardcode the search (i.e. substitute /'adair/' for the $countyname in the "LIKE" statement) and get results, so the search works. How do I create a query using that $countyname variable? I'm sure it's easy.

The errors I get are either unexpected t string variable or no matches.

Here's the form:

Here's the code, anyone see the problem?

$countynam= $_HTTP_POST_VARS ['countyname'];
$dbh = mysql_connect ("xxxxx", "xxxxx", "xxxxx") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("xxxxx");
$query = 'SELECT * '
. ' FROM Kentucky '
. ' WHERE 1 AND county '
. ' LIKE $countynam ';
$result = mysql_query($query) or die ('Your search has no matching counties');

Mordecai

Put the variable outside the quotes:
'string '.$variable

That should fix it.

jollyollie

I tried a number of outside the quote variations to no avail.

    . ' LIKE '  $countyname  ;

returns unexpected tvariable

    . ' LIKE ' . $countyname .  ;

return unexpected ;

    . ' LIKE ' . $countyname . ' ' ;

returns no matches

Sorry to be such a neophyte pain.

TheDefender

This:
$countynam= $_HTTP_POST_VARS['countyname'];

Should actually be this:
$countynam= $HTTP_POST_VARS['countyname'];

BUT, I highly recommend that you actually use this instead:
$countynam= $_POST['countyname'];

Look in the PHP Manual at Predefined Variables, for an explanation of why I suggest you use the calls to the $_POST array instead of using $HTTP_POST_VARS

Mordecai

So it should. For some reason, I didn't even see that line at all... 😃

Use the last line of your three attempts plus TheDefender's change and it will work.

TheDefender

That's ok Mordecai. I didn't see it either until I copy and pasted the code into my trusty color-coded IDE...😉

jollyollie

Thank you both for your help. I'm feeling very frustrated in trying to understand PHP and am thinking that it is probably something quite easy to fix, but darned if I can figure it out

I'm still getting an error (Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource). It does perform the echo command though, go figure. I'm posting the whole code, just to see if there's a problem elsewhere.

This is raising some other questions for me too. First, is setting $countynam redundant? can I just use $countyname (sorry to make the variable names so similar.) Second, can I make it a search using part of the word (i.e. putting % before and after, and how on earth would I format that?)

The goal of the script is to search through a database to find all towns within a given county. Have I over-complicated things?

<?php
//set the number of columns
$columns = 2;
$countynam= $_POST['countyname'];
$dbh = mysql_connect ("**", "", "") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("**");
$query = 'SELECT * '
. ' FROM Kentucky '
. ' WHERE 1 AND county '
. ' LIKE ' . $countynam . ' ' ;
$result = mysql_query($query);
//we add this line because we need to know the number of rows
$num_rows = mysql_num_rows($result);
echo $countyname ;
echo "<TABLE BORDER=\"0\">\n";

//changed this to a for loop so we can use the number of rows
for($i = 0; $i < $num_rows; $i++) {
$row = mysql_fetch_array($result);
if($i % $columns == 0) {
//if there is no remainder, we want to start a new row
echo "<TR>\n";
}
echo "<TD>" . $row['zip'] . " ";
echo " " . $row['city'] . "</TD>\n";
if(($i % $columns) == ($columns - 1) || ($i + 1) == $num_rows) {
//if there is a remainder of 1, end the row
//or if there is nothing left in our result set, end the row
echo "</TR>\n";
}
}
echo "</TABLE>\n";
?>

jollyollie

Ok, brainstorm, I added the following "or" statement to the $result query:

or die ('I cannot do this because: ' . mysql_error())

I used the search term "adair" (which I know is a valid county name) and got the following response

I cannot do this because: Unknown column 'adair' in 'where clause'

Does this help?

liquidSilver

ok, i did the same thing with this function. I took the input froma form from the user and then searche through the db to find all the matches. This funcitons looks a little c omplex put it dynamically displays all the colum names and number of colums. so no matter what you do with the db it will automatically be refleced in the table that is output.

it also display the colum names as links so that the user can click on the links and sort my that colum asc or dsc. my results come up on multiple pages and so there is a part for jumping to other pages.

take your time and look through this and then rethink how you are coding your script. you don't want to have to write all of it again, ir you change your db. for example if you add antoher colum you are going to have to go back into your code and hardcode it. this dynamically pulls the mysql_num_cols out of the table nad display the correct number.


function display_record($table, $query, $title, $num_of_rows, $num_of_pages, $searchField, $searchString, $order_col, $order_dir, $start_record, $page_num, $category, $group_num)
{
	$conn = db_connect();
	if(!$conn)
		return "Could not connect to databse at this time - please try again later.";

$result = mysql_query($query);
if (!$result)
{
	echo "<font face=\"Tahoma\" size=\"3\">Please Enter Your Search Criteria Above...";
}

else if ($result)
{
	$numFields = mysql_num_fields($result);
	$numRows = mysql_num_rows($result);
	$myrow = mysql_fetch_array($result);

	echo "<table  border=\"0\" width=\"50%\" cellspacing=\"3\" valign=\"top\">";
	echo "<tr><td colspan=\"11\" height=\"2\" class=formFieldName align=\"left\" valign=\"top\"><font><b>$title</b></font></td></tr><tr>";
	echo "<td width=\"40\" align=\"center\" bgcolor=\"f3f3f3\"><font face=\"Tahoma\" size=\"2\">Actions</font></td>";
	if (strcmp($category, "search")== 0 || strcmp($category, "userdel") == 0)
	{
		for($i = 0; $i < $numFields; $i++)
		{	
			if (strcmp($order_dir, "DESC") == 0)
			{
				$arrow = "images/downArrow.gif";
			}
			else
			{
				$arrow = "images/upArrow.gif";
			}
			if (mysql_field_name($result, $i) == $order_col)
			{
				printf ("<td align=\"center\" width=\"120\" bgcolor=\"#f3f3f3\" class=formFieldName valign=\"top\"><font face=\"Tahoma\" size=\"2\"><a href=\"search.php?page_num=%s&searchFeild=%s&searchString=%s&order_col=%s&order_dir=%s\">%s</a>&nbsp;&nbsp;<img src=\"%s\" valign=\"top\" border=\"0\"></font></td>", $page_num, $searchField, $searchString, mysql_field_name($result, $i), $order_dir, mysql_field_name($result, $i), $arrow);	
			}
			else 
			{
				printf ("<td align=\"center\" width=\"120\" bgcolor=\"#f3f3f3\" class=formFieldName valign=\"top\"><font face=\"Tahoma\" size=\"2\"><a href=\"search.php?page_num=%s&searchFeild=%s&searchString=%s&order_col=%s&order_dir=%s\">%s</a>&nbsp;&nbsp;</font></td>", $page_num, $searchField, $searchString, mysql_field_name($result, $i), $order_dir, mysql_field_name($result, $i));	
			}
		}
	}

	else if (strcmp($category, "add") == 0)
	{
		for($i = 0; $i < $numFields; $i++)
		{	
			printf ("<td align=\"center\" width=\"120\" bgcolor=\"#f3f3f3\" class=formFieldName valign=\"top\"><font face=\"Tahoma\" size=\"2\">%s</td>", mysql_field_name($result, $i));	
		}
	}
	else if (strcmp($category, "del") == 0)
	{
		for($i = 0; $i < $numFields; $i++)
		{	
			if ($i == 0)
			{
				$org_serial = $myrow[0];
			}

			printf ("<td align=\"center\" width=\"120\" bgcolor=\"#f3f3f3\" class=formFieldName valign=\"top\"><font face=\"Tahoma\" size=\"2\">%s</td>", mysql_field_name($result, $i));	
		}
	}

	echo "<tr><td></td></tr>";

	if (strcmp($category, "userdel") == 0)
	{
		for($i = 0; $i < $numRows; $i++)
		{
		printf  ("<tr><td align=\"center\" bgcolor=\"#f3f3f3\" >&nbsp;&nbsp;<font face=\"Tahoma\" size=\"2\"><a href=\"add.php?serial_num=%s&edit=1\"><img src=\"images\edit.gif\" alt=\"Edit Record\" border=\"0\"></a>&nbsp;&nbsp;<a href=\"add.php?serial_num=%s&delete=1\"><img src=\"images\delete.gif\" alt=\"Delete Record\" border=\"0\"></a>&nbsp;&nbsp;<font></td>", $myrow[0], $myrow[0]);
		for($j = 0; $j < $numFields; $j++)
		{
			printf ("<td align=\"center\" width=\"120\" bgcolor=\"#f3f3f3\" class=display_record valign=\"top\"><font face=\"Tahoma\" size=\"2\">%s</font></td>\n\n", $myrow[$j]);
		}
		$myrow = mysql_fetch_array($result); 
		echo "</tr>";
		}
	}

	else 
	{
		for($i = 0; $i < $numRows; $i++)
		{
			printf  ("<tr><td align=\"center\" bgcolor=\"#f3f3f3\" >&nbsp;&nbsp;<font face=\"Tahoma\" size=\"2\"><a href=\"add.php?serial_num=%s&edit=1\"><img src=\"images\edit.gif\" alt=\"Edit Record\" border=\"0\"></a>&nbsp;&nbsp;<a href=\"add.php?serial_num=%s&delete=1\"><img src=\"images\delete.gif\" alt=\"Delete Record\" border=\"0\"></a>&nbsp;&nbsp;<font></td>", $myrow[0], $myrow[0]);
			for($j = 0; $j < $numFields; $j++)
			{
				if ($j == 2) 
				{   
					$myrow[$j] = date("m/d/y", $myrow[$j]);

					if (strcmp($myrow[$j], "12/31/69") == 0)
					{
						$myrow[$j] = "Not Available";
					}
				}

				if (strcmp($myrow[$j], "") == 0)
				{
					$myrow[$j] = "--";
				}

				printf ("<td align=\"center\" width=\"120\" bgcolor=\"#f3f3f3\" class=display_record valign=\"top\"><font face=\"Tahoma\" size=\"2\">%s</font></td>\n\n", $myrow[$j]);
			}
			$myrow = mysql_fetch_array($result); 
			echo "</tr>";
		}
	}

	echo "<tr>\n<td colspan=\"4\" height=\"10\" valign=\"top\"></td>\n</tr>\n\n";
	echo "<tr>\n<td width=\"2\"></td><td colspan=\"2\" valign=\"top\"><font></font></td><td colspan=\"2\">\n<p align=\"right\"></p>\n</td>\n</tr>\n\n</table>\n\n";
	echo "<div align=\"left\">";

	if (strcmp($category, "search") == 0)
	{
		if ($num_of_pages < 10)
		{
			if ($num_of_rows < 20)
			{
				printf ("<b><font face=\"Tahoma\" color=\"#98A39A\" size=\"2\">Displaying Results %s-%s  out of %s, comprised of %s Pages", $start_record, $num_of_rows, $num_of_rows, $num_of_pages);
			}
		}

		else 
		{
			printf ("<b><font face=\"Tahoma\" color=\"#98A39A\" size=\"2\">Displaying Results %s-%s  out of %s, comprised of %s Pages", $start_record, $start_record + 19, $num_of_rows, $num_of_pages);
		}	
	}

	else if (strcmp($category, "del") == 0)
	{
		printf ("<b>&nbsp;&nbsp;<font class=warning>WARNING<br>&nbsp;&nbsp;This record will be deleted!&nbsp;&nbsp;<br><br>&nbsp;&nbsp;<a class=body_link href=\"add.php?delete=2&org_serial=$org_serial\">Click here to delete.</a>");
	}
}

if (strcmp($category, "search") == 0 || strcmp($category, ""))
{
	display_pages($num_of_rows, $searchField, $searchString, $num_of_pages, $group_num, $order_dir, $order_col);
	echo "</div>";
}
mysql_close($conn);
}

liquidSilver

oh yeah that part at the bottom is for the admin of the portal to delte or chage records... just don't worry about that part. good luck with that chunk of code...

and btw i wrote that a long time I'm sure there is a little more effiecent way to do it now that i am lookign at it.....

my advice.. get a good php begining book and start at the begining and read the whole thing and do the tutorials. That will help you alot. Good Luck!!

jollyollie

LiquidSilver,

The problem isn't in the format of the output, I can get it to show up in the right format by hardcoding the search term, the problem is in using a variable in the search statement. The data doesn't show up because the search does not work.

Maybe I missed the search statement in your code. I'm looking forward to using your example as help in learning the many tools available in PHP. Thank you.

jollyollie

Ok, I figured out the problem. It has to do with adding quotes. I'm using the following search query:

$query = 'SELECT * '
. ' FROM Kentucky '
. ' WHERE county '
. ' LIKE \'' . $countyname . '\'' ;

Now it works. Thanks to all!