php and mysql problems

burnsy · Jan 29, 2006

i'm trying to get a search form for mysql to work to search top gear episode information. i'm using this template:

Code:

<?php
if ($searchstring)
{
 $sql="SELECT * FROM episodes WHERE Car LIKE "\%$search%\"";
 $db = mysql_connect("localhost", "root", "therunner");
 mysql_select_db("test",$db);
 $result = mysql_query($sql,$db);
 echo "<TABLE BORDER=2>";
 echo"<TR><TD>[B]Full Name[/B]<TD>[B]Nick Name[/B]<TD>[B]Options[/B]</TR>";
 while ($myrow = mysql_fetch_array($result))
 {
  echo "<TR><TD>".$myrow["Episode"]."</td><td>".$myrow["Date"]."</td><TD>".$myrow["Time"]."</td><TD>".$myrow["Type"]."</td><TD>".$myrow["Car"]."</td><TD>".$myrow["Description"]."</td><TD>".$myrow["Presenter"]."</td></tr>";
 }
 echo "</TABLE>";
}
else
{
echo "Enter Search";
}
{

?>
<form method="POST" action="<?php $PHP_SELF ?>">
<table border="2" cellspacing="2">
<tr><td>Insert you search string here</td>
</tr>
<tr>
<td><input type="text" name="search" size="28"></td>
</tr>
</table>


<input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></p>
</form>
<?php
}
?>

in my code, i added a print to the else, and no matter what i put into the text box, it comes back with the else. what am i missing?

jeffy777 · Jan 29, 2006

ok - at a first glance I see one change:

Code:

$sql="SELECT * FROM episodes WHERE Car LIKE "\%$search%\"";

should be changed to:

Code:

$sql="SELECT * FROM episodes WHERE Car LIKE '%$search%'";

I'll keep looking, that's just the first mistake I've found

And do you really want to post your MySQL login info online???

EDIT:

Something else: why do you have the form in curly brackets?

You should change this:

Code:

else
{
echo "Enter Search";
}
{

to this:

Code:

else
{
echo "Enter Search";

Getting rid of un-needed brackets. Unless you want the search form to appear always on the bottom? But the way you have it set up isn't correct for that.

It would be easier for me to write the code over than to correct yours

EDIT:

The reason it keeps loading the else is because the variable $searchstring is never defined from what I can see in your code.

burnsy · Jan 29, 2006

that would be neat, you'd have to show me what you did. i can't find why the script would not work though. even i can see that the syntax should be right, except for the first part.

jeffy777 · Jan 29, 2006

Give me a few minutes and I'll write a new code for you and explain it, if need be

burnsy · Jan 29, 2006

if you could, could you also find out why my code wouldn't work? if you've got time. here's the code running now:http://burnsy.no-ip.info/topgear/search.php

jeffy777 · Jan 29, 2006

k, I'm just about done writing this new code for you

burnsy · Jan 29, 2006

man, you're too nice.

jeffy777 · Jan 29, 2006

ok, I just tested this on a server I have access to and I setup a test DB and it works great:

Code:

<?php

if(isset($search))
{

// ------------ CONNECT TO THE DB ------------ //

$db = mysql_connect("localhost", "root", "therunner");
mysql_select_db("test",$db);

// ------------ RUN THE SEARCH QUERY ------------ //

$query = "SELECT * FROM episodes WHERE Car LIKE '%$search%'";
$result=mysql_query($query);

?>

<html>
<head>
<title>My Search Results</title>
</head>

<body>


<table border="2" cellpadding="5" width="100%">

	<tr>
		<td>[b]Episode[/b]</td>
		<td>[b]Date[/b]</td>
		<td>[b]Time[/b]</td>
		<td>[b]Type[/b]</td>
		<td>[b]Car[/b]</td>
		<td>[b]Description[/b]</td>
		<td>[b]Presenter[/b]</td>
	</tr>

<?


// ------------ DEFINE THE VARIABLES ------------ //

	while($row = mysql_fetch_array($result))
		{

		$Episode     = $row['Episode'];

		$Date        = $row['Date'];

		$Time        = $row['Time'];

		$Type        = $row['Type'];

		$Car         = $row['Car'];

		$Description = $row['Description'];

		$Presenter   = $row['Presenter'];

// ------------ SHOW THE RESULTS ------------ //

?>

	<tr>
		<td><? echo $Episode; ?></td>
		<td><? echo $Date; ?></td>
		<td><? echo $Time; ?></td>
		<td><? echo $Type; ?></td>
		<td><? echo $Car; ?></td>
		<td><? echo $Description; ?></td>
		<td><? echo $Presenter; ?></td>
	</tr>


<?

		}

	?>

</table>






<form action="<?php $PHP_SELF ?>" method="post">

[b]Search Again? [/b]
<input type="text" name="search"> <input type="Submit" value="Begin Search">
</form>






</body>
</html>

<?

mysql_close();

}

// ------------ SHOW THE FORM BY ITSELF IF A SEARCH HASN'T STARTED------------ //

else

{

?>

<html>
<head>
<title>My Search</title>
</head>

<body>






<form action="<?php $PHP_SELF ?>" method="post">

[b]Type in a Car[/b]
<input type="text" name="search"> <input type="Submit" value="Begin Search">
</form>






</body>
</html>

<?

}

?>

Please let me know how it turns out for you

It was a quick hack, so the code could be a little cleaner, but it works and that's what's important, eh?

burnsy · Jan 29, 2006

this is awesome. i see that the definitions was one thing missing. i'll test it now.

burnsy · Jan 29, 2006

hmm, just returns nothing back. same problem that i had. i can only assume that i've got php.ini parameters wrong. http://burnsy.no-ip.info/topgear/search.php

jeffy777 · Jan 29, 2006

The definitions weren't required, but I do it out of habit

Here I got yours to work too. I made the changes I described above and changed $searchstring to $search

Code:

<?php
if ($search)
{
 $sql="SELECT * FROM episodes WHERE Car LIKE '%$search%'";
 $db = mysql_connect("localhost", "root", "therunner");
 mysql_select_db("test",$db);
 $result = mysql_query($sql,$db);
 echo "<TABLE BORDER=2>";
 echo"<TR><TD>[B]Full Name[/B]<TD>[B]Nick Name[/B]<TD>[B]Options[/B]</TR>";
 while ($myrow = mysql_fetch_array($result))
 {
  echo "<TR><TD>".$myrow["Episode"]."</td><td>".$myrow["Date"]."</td><TD>".$myrow["Time"]."</td><TD>".$myrow["Type"]."</td><TD>".$myrow["Car"]."</td><TD>".$myrow["Description"]."</td><TD>".$myrow["Presenter"]."</td></tr>";
 }
 echo "</TABLE>";
}
else
{
echo "Enter Search";

?>
<form method="POST" action="<?php $PHP_SELF ?>">
<table border="2" cellspacing="2">
<tr><td>Insert you search string here</td>
</tr>
<tr>
<td><input type="text" name="search" size="28"></td>
</tr>
</table>


<input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></p>
</form>
<?php
}
?>

burnsy · Jan 29, 2006

, don't understand why it won't work. i've got plenty of databases running just fine. i'm gonna check my settings.

burnsy · Jan 29, 2006

the query works if i put it into phpmyadmin directly. the form seems to not be passing the argument to the $search parameter.

jeffy777 · Jan 29, 2006

Maybe you have Register Globals turned off? Plus there was an error with the way you were using the PHP_SELF, I didn't catch it the first time. Try this code then, it will work with register globals off:

Code:

<?php

if(isset($_POST['search']))

{

$search = $_POST['search'];

// ------------ CONNECT TO THE DB ------------ //

$db = mysql_connect("localhost", "root", "therunner");
mysql_select_db("test",$db);

// ------------ RUN THE SEARCH QUERY ------------ //

$query = "SELECT * FROM episodes WHERE Car LIKE '%$search%'";
$result=mysql_query($query);

?>

<html>
<head>
<title>My Search Results</title>
</head>

<body>


<table border="2" cellpadding="5" width="100%">

	<tr>
		<td>[b]Episode[/b]</td>
		<td>[b]Date[/b]</td>
		<td>[b]Time[/b]</td>
		<td>[b]Type[/b]</td>
		<td>[b]Car[/b]</td>
		<td>[b]Description[/b]</td>
		<td>[b]Presenter[/b]</td>
	</tr>

<?


// ------------ DEFINE THE VARIABLES ------------ //

	while($row = mysql_fetch_array($result))
		{

		$Episode     = $row['Episode'];

		$Date        = $row['Date'];

		$Time        = $row['Time'];

		$Type        = $row['Type'];

		$Car         = $row['Car'];

		$Description = $row['Description'];

		$Presenter   = $row['Presenter'];

// ------------ SHOW THE RESULTS ------------ //

?>

	<tr>
		<td><? echo $Episode; ?></td>
		<td><? echo $Date; ?></td>
		<td><? echo $Time; ?></td>
		<td><? echo $Type; ?></td>
		<td><? echo $Car; ?></td>
		<td><? echo $Description; ?></td>
		<td><? echo $Presenter; ?></td>
	</tr>


<?

		}

	?>

</table>






<form action="<? echo $_SERVER['PHP_SELF']; ?>" method="post">

[b]Search Again? [/b]
<input type="text" name="search"> <input type="Submit" value="Begin Search">
</form>






</body>
</html>

<?

mysql_close();

}

else 

{

?>

<html>
<head>
<title>My Search Results</title>
</head>

<body>






<form action="<? echo $_SERVER['PHP_SELF']; ?>" method="post">

[b]Type in a Car[/b]
<input type="text" name="search"> <input type="Submit" value="Begin Search">
</form>






</body>
</html>

<?

}

?>

burnsy · Jan 29, 2006

dude, you are awesome. don't you just hate one little thing messing it all up? thanks again.

burnsy · Jan 29, 2006

does this syntax look right?

Code:

$query = "SELECT * FROM episodes ";
  if($select == "Episode")
     $query .= "WHERE Episodes LIKE '%$search%'";
  if($select == "Date")
     $query .= "WHERE Date LIKE '%$search%'";
  if($select == "Time")
     $query .= "WHERE Time LIKE '%$search%'";
  if($select == "Type")
     $query .= "WHERE Type LIKE '%$search%'";
  if($select == "Car")
     $query .= "WHERE Car LIKE '%$search%'";
  if($select == "Description")
     $query .= "WHERE Description LIKE '%$search%'";
  if($select == "Presenter")
     $query .= "WHERE Presenter LIKE '%$search%'";

i put print commands for $select and $search, search comes though, but select does not. select is the name of a select form.

jeffy777 · Jan 29, 2006

It's hard to say without looking at your entire code after you modified it. But you could just use the $search form and change the orignal query to this:

Code:

$query = "SELECT * FROM episodes WHERE Episodes LIKE '%$search%' OR Date LIKE '%$search%' OR Time LIKE '%$search%' OR Type LIKE '%$search%' OR Car LIKE '%$search%' OR Description LIKE '%$search%' OR Presenter LIKE '%$search%' ORDER BY Date";

I almost like that better becasue they can search any field without having to specify one particular one. Then I put the ORDER BY in there as well to tidy up the results.

There is a lot more you could do with this: allow the results to be sortable by the columns and the order (ascending, descending), show "No Results to Display" if nothing comes back, break the results up into sections, like "3 results found in Descriptions" and "4 results found in Presenter", etc. etc.

And you could always get some ideas from the wonderful search layout that Viper uses:
http://www.finalgear.com/search.php

I think the structure is very nice :thumbup:

burnsy · Jan 29, 2006

i can do those misc. stuff you mentioned. that query works, but for some reason, doesn't print the episode. prints everthing else. why? http://burnsy.no-ip.info/topgear/episode_search.php.

i just want to get this working, then i can go about with the layout and stuff. if i put the same query into phpmyadmin, it will return the episode.

jeffy777 · Jan 29, 2006

Paste your entire code so I can see where the problem is

burnsy · Jan 29, 2006

Code:

<?php
if(isset($_POST['search']))
{
$search = $_POST['search'];
// ------------ CONNECT TO THE DB ------------ //
$db = mysql_connect("localhost", "root", "therunner");
mysql_select_db("test",$db);
// ------------ RUN THE SEARCH QUERY ------------ //
$query = "SELECT * FROM episodes WHERE Episode LIKE '%$search%' OR Date LIKE '%$search%' OR Time LIKE '%$search%' OR Type LIKE '%$search%' OR Car LIKE '%$search%' OR Description LIKE '%$search%' OR Presenter LIKE '%$search%'";
$result=mysql_query($query);
print($search);
print($query);
?>
<html>
<head>
<title>My Search Results</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#CCCCCC">
<span class="style1"><? echo $Episode; ?></span>
<table width="100%" border="0" cellpadding="5">
   <tr bgcolor="#FFFFFF">
      <td>[b]Episode[/b]</td>
      <td>[b]Date[/b]</td>
      <td>[b]Time[/b]</td>
      <td>[b]Type[/b]</td>
      <td>[b]Car[/b]</td>
      <td>[b]Description[/b]</td>
      <td>[b]Presenter[/b]</td>
   </tr>
<?
// ------------ DEFINE THE VARIABLES ------------ //
   while($row = mysql_fetch_array($result))
      {
      $Episode     = $row['Episode'];
      $Date        = $row['Date'];
      $Time        = $row['Time'];
      $Type        = $row['Type'];
      $Car         = $row['Car'];
      $Description = $row['Description'];
      $Presenter   = $row['Presenter'];
// ------------ SHOW THE RESULTS ------------ //
?>
   <tr bgcolor="#FFFFFF">
      <td></td>
      <td><? echo $Date; ?></span></td>
      <td><? echo $Time; ?></span></td>
      <td><? echo $Type; ?></span></td>
      <td><? echo $Car; ?></span></td>
      <td><? echo $Description; ?></span></td>
      <td><? echo $Presenter; ?></span></td>
   </tr>
<?
      }
   ?>
</table>




<form action="<? echo $_SERVER['PHP_SELF']; ?>" method="post">
[b]Search Again?

[/b]
<input type="text" name="search">


<input type="Submit" value="Begin Search">
</form>




</body>
</html>
<?
mysql_close();
}
else
{
?>
<html>
<head>
<title>My Search Results</title>
</head>
<body>




<form action="<? echo $_SERVER['PHP_SELF']; ?>" method="post">

[b]Search?

[/b]
<input type="text" name="search">


<input type="Submit" value="Begin Search">
</form>




</body>
</html>
<?
}
?>

php and mysql problems

Well-Known Member

Forum Addict

Well-Known Member

Forum Addict

Well-Known Member

Forum Addict

Well-Known Member

Forum Addict

Well-Known Member

Well-Known Member

Forum Addict

Well-Known Member

Well-Known Member

Forum Addict

Well-Known Member

Well-Known Member

Forum Addict

Well-Known Member

Forum Addict

Well-Known Member