PHP Frustration

Dev Zone Code
1

Im trying to get an SQL query to work but im having problems. I create a form to submit the search:

<form action="" method="post">

<input type="submit"></input>

<select name="category">

<option value="all">All</option>

<option value="id">ID</option>

<option value="firstname">First Name</option>

<option value="lastname">Last Name</option>

<option value="dob">DOB</option>

</select>

<input name="searchTerm" type="test"></input>

</form>

and the php:

$category = $ POST['category'];

$searchTerm = $ POST['searchTerm'];

$con=mysqli_connect("localhost","root","","158_252");

// Check connection

if (mysqli_connect_errno())

{

echo "Failed to connect to MySQL: " . mysqli_connect_error();

}

 

if ($category = "all"){

$result = mysqli_query($con,"SELECT * FROM catalogue");

}

else{

$result = mysqli_query($con,"SELECT * FROM catalogue WHERE $category = $searchTerm");

}

there is a bit more php code but the problem occurs somewhere in here

i want to check if $category is value 'all' at which display everything in the SQL Database. no matter what the form values are set to, it always outputs the whole database, 

i think its my syntax with PHP as im new to it so any help is appreciated.

2

havent done PHP in a while but what happens if you echo the $category?

3

You have an assignment instead of a comparison in the second if clause. You also should rather check the connection handle ($con) instead of the error number. Use mysql_error for error handling and use mysql_real_escape_string to prevent SQL injection.

Or just use PDO.

4

<form action="" method="post">

Firstly you have no action, have you removed it for posting the code here? Or is the PHP being performed on the same page as the HTML?

$result = mysqli_query($con,"SELECT * FROM catalogue WHERE $category = $searchTerm");

This should be, $result = mysqli_query($con,"SELECT * FROM catalogue WHERE category = '"$searchTerm"' " );

As you are currently calling the post category ($category) instead of the field name within the database.


<input name="searchTerm" type="test"></input>

Little tip, this can be written as <input name="searchTerm" type="test" />

 

5

$result = mysqli_query($con,"SELECT * FROM catalogue WHERE $category = $searchTerm");

This should be, $result = mysqli_query($con,"SELECT * FROM catalogue WHERE category = '"$searchTerm"' " );

Just re-read the OP and mine and realised what you want to be searching for (and a small mistake in my code)

  $result = mysqli_query($con,"SELECT * FROM catalogue WHERE '".$category."' = '"$.searchTerm."' " );

I realised your searching for a value in a select field :P

6

So i corrected this to '==' which i assume it right (im new to PHP). this then throws an error later on when i try to print the results in a table:

while($row = mysqli_fetch_array($result))

{

print("<tr>");

print("<td>" . $row['ID'] . "</td><td>" . $row['FirstName'] . "</td><td>" . $row['LastName'] . "</td><td>" . $row['DOB'] . "</td>");

print("</tr>");

}

 

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\Catalog\index.php on line 56
7

returns syntax error

I changed it to:

 $result = mysqli_query($con,"SELECT * FROM catalogue WHERE $category = '$searchTerm' ");

and this has fixed it and is displaying results now. CHEERS! :)

8

Solution has been found. Cheers