mysql error at line 1

doood · March 22, 2007

I'm pretty new at this, this is actually for an assignment I have to turn in. I have included the contents of my .sql file and the php query I am trying to get to display.

Write the corresponding php target script. Specifications:

* Use the input fields to query your table for matches -- for example, if the user enters values in both the name and city fields, matching table rows would match both values entered

* If a field is blank, do not use it to filter values

* Print out a nicely formatted xhtml table that contains all matching table rows

I know it isn't formatted just yet but I'll worry about that once it displays the above

I don't even know if I'm doing the best or most efficient way...

I have a xhtml form that is using the post method and is sending the information to this code, I will post just in case.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '='Pamela'' at line 1

Is the error I'm getting.

The php script works when I comment out all the if statements and just do a query = "Select * FROM lab5";

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>

<title>Lab 5</title>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<meta name="Author" content="" />
</head>

<body>
<table>
<form action="lab5.php" method="post">
<tr>
	<td>NAME: </td>
	<td> <input type="text" name="name" size="20" /></td>
</tr>

<tr>
	<td>ADDRESS: </td>
	<td> <input type="text" name="address" size="30" /></td>
</tr>

<tr> 
	<td>CITY: </td>
	<td> <input type="text" name="city" size="15" /></td>
</tr>

<tr>
	<td>STATE: </td>
	<td> <input type="text" name="state" size="5" /></td>
</tr>

<tr> 
	<td>ZIP: </td>
	<td> <input type="text" name="zip" size="7" /></td>

</tr>

<tr>
<td>Buttons: </td>
<td>	
<div style="text-align: center">
<input type="submit" value="search"  />
<input type="reset" value="clear form" />
</div>
</td>
</tr>

</form>
</table>



</body>
</html>

php code

<?php

        include 'connect.php';

        mysql_select_db("pserver_ITSE2302")
        or die(mysql_error());

	$name = $_POST['name'];
	$address = $_POST['address'];
	$city = $_POST['city'];
	$state = $_POST['state'];
	$zip = $_POST['zip'];
   
       if($name != "") 
       {	
        $query = "SELECT *" 
    . "FROM lab5"
        . "WHERE (name ='$name')";
        }
        
        if($address != "")
        {
        $query = "SELECT *"
        . "FROM lab5"
        . "WHERE (address = '$address')";
        }

        if($city != "")
        {
        $query = "SELECT *"
        . "FROM lab5"
        . "WHERE (city= '$city')";
        }
        
       if($state != "")
        {
        $query = "SELECT *"
        ."FROM lab5"
        ."WHERE (state= '$state')";
        }

        if($zip != "")
        {
    $query = "SELECT *"
        ."FROM lab5"
        ."WHERE (zip= $zip)";
        }




        $result = mysql_query($query) 
        or die (mysql_error());


        if($result)
        {
        while($row = mysql_fetch_array($result))
            {
              $name = $row['name'];
              $address = $row['address'];
              $city = $row['city'];
              $state = $row['state'];
              $zip = $row['zip'];
          
              echo "<table>";
              echo "<tr>";
              echo "<td>";
              echo $name;
              echo "</td>";
              echo "<td>";
              echo $address;
              echo "</td>";
		  echo "<td>";
		  echo $city;
		  echo "</td>";
		  echo "<td>";
		  echo $state;
		  echo "</td>";
		  echo "<td>";
		  echo $zip;
		  echo "</td>";
              echo "</tr>";
              echo "</table>";
            }
         }
       else 
       echo "Could not retrieve records: %s\n", mysql_error($connect);

            


        ?>

mysql didn't have any trouble importing this information

-- MySQL dump 10.10
--
-- Host: localhost    Database: ITSE2302
-- ------------------------------------------------------
-- Server version	5.0.27

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `lab5`
--

DROP TABLE IF EXISTS `lab5`;
CREATE TABLE `lab5` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(20) NOT NULL,
  `address` varchar(30) NOT NULL,
  `city` varchar(20) NOT NULL,
  `state` char(2) NOT NULL,
  `zip` char(5) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `lab5`
--

LOCK TABLES `lab5` WRITE;
/*!40000 ALTER TABLE `lab5` DISABLE KEYS */;
INSERT INTO `lab5` VALUES (1,'Fred Flinstone','200 Bedrock','San Antonio','TX','78212'),(2,'Barney Rubble','201 Bedrock','San Antonio','TX','78212'),(5,'Pamela Anderson','7770 Parkway Ave','San Antonio','TX','78212');
/*!40000 ALTER TABLE `lab5` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2007-03-20 23:33:35

skali · March 23, 2007

$query = "SELECT * FROM lab5 WHERE address = '$address'";

doood · March 23, 2007

hrmm, well I changed all my queries to look like that but now it doesn't return anything at all

and when I leave the fields blank it says no query was returned.

btherl · March 23, 2007

The problem before was that you were sticking strings together with no spaces, so your queries would look like this:

SELECT *FROM lab5WHERE address = '$address'

As for what your problem is now.. try printing out the query you are about to run, to make sure it looks like you expect it to.

doood · March 23, 2007

<?php

        include 'connect.php';

        mysql_select_db("pserver_ITSE2302")
        or die(mysql_error());

	$name = $_POST['name'];
	$address = $_POST['address'];
	$city = $_POST['city'];
	$state = $_POST['state'];
	$zip = $_POST['zip'];
   
        if($name != "") 
        $query = "SELECT * FROM lab5 WHERE name = '$name'";
        
        if($address != "")        
        $query = "SELECT * FROM lab5 WHERE address = '$address'";
       
        if($city != "")
        $query = "SELECT * FROM lab5 WHERE city = '$city'";
        
        if($state != "")
        $query = "SELECT * FROM lab5 WHERE state = '$state'";

        if($zip != "")
        $query = "SELECT * FROM lab5 WHERE zip = '$zip'";



        $result = mysql_query($query) 
        or die (mysql_error());


        if($result)
        {
        while($row = mysql_fetch_array($result))
            {
              $name = $row['name'];
              $address = $row['address'];
              $city = $row['city'];
              $state = $row['state'];
              $zip = $row['zip'];
          
              echo "<table>";
              echo "<tr>";
              echo "<td>";
              echo $name;
              echo "</td>";
              echo "<td>";
              echo $address;
              echo "</td>";
		  echo "<td>";
		  echo $city;
		  echo "</td>";
		  echo "<td>";
		  echo $state;
		  echo "</td>";
		  echo "<td>";
		  echo $zip;
		  echo "</td>";
              echo "</tr>";
              echo "</table>";
            }
         }
       else 
       echo "Could not retrieve records: %s\n", mysql_error($connect);

            


        ?>

That's what my code looks like now and I think it's working completely but now I just don't know how to filter out results, like if the user specifies 3 fields or more, I'm not sure how to build up the query to where only that one person being searched for is displayed.

fenway · March 23, 2007

Have an where clause array, and have each of the pieces thaat the user requested added to this array, then join this with " OR ", and then pre-pend "SELECT * FROM lab5 WHERE " if this array has length.

doood · March 23, 2007

hrmm sounds way beyond my capabilities, but I'll give it a shot, thanks

fenway · April 4, 2007

hrmm sounds way beyond my capabilities, but I'll give it a shot, thanks

This is fairly simple PHP work... if you're having trouble, direct any question to that forum.

Sign In

mysql error at line 1

Recommended Posts

doood

Link to comment

Share on other sites

skali

Link to comment

Share on other sites

doood

Link to comment

Share on other sites

btherl

Link to comment

Share on other sites

doood

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

doood

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information