PHP Complex search

Question

Scenario:

So earlier, I made a request asking for ways on how to transform my four simple search queries for:

"Name", "Price", "availability" and "Category"

into one complex search that would take all of those database tables into account.

I think I managed to finally construct an SQL query that takes all of my categories into account and I tested the query in php_myadmin and it seemed to work as I needed it too.

Problem:

My problem is, I seem to be getting an error when trying to run my code, I don't think it's really anything to do with my new SQL query so I don't know what's up.

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\Users\Method\Desktop\MethodProject\Server\htdocs\PerfectoWebsite\SearchByAllCriteria.php on line 117

My error wants mysqli_result as a parameter and gets a boolean, although this code has worked for all my other searches thus far without error, so I'm pretty certain something before this code is resulting in an error.

I've managed to figure out the error is somewhere within this section of PHP, I'm just unsure where:

<?php include "ConnectToServer.php" ?>
    <?php include "ConnectToDatabase.php" ?>

<?php

//check to make sure the form has been submitted and retrieve the contents of 
//both things and make variables.

if(isset($_POST['search'])){
$get_price=$_POST['price'];
$get_function=$_POST['Operation'];
$get_name=$_POST['UserEnteredText'];
$get_Availability=$_POST['AvailabilityOperation'];
$get_Category=$_POST['CategoryOperation'];
//create the query to extract the data from the table and store
//the SQL query in a variable called $query
/*$query="SELECT * FROM friends WHERE name = '$get_name'";*/


$query="SELECT * FROM product WHERE price $get_function $get_price 
        AND SELECT * FROM product WHERE avaliability = $get_Availability 
        AND SELECT * FROM product WHERE category = $get_Category
        AND SELECT * FROM product WHERE product name = $get_name";

//the result set of the mysql_query function is then stored in
//an array called $result
$result=mysqli_query($connection, $query);
//create while loop and loop through result set, reading each one 
//and displaying it through the use of an echo command

>>>line 117<<<while ($row = mysqli_fetch_array($result)) {

As before, I'd greatly appreciate assistance, I'm pretty much a noob at this and I really want to understand why these errors are happening so I can prevent them in the future.

Thanks.

[Edit]:

I added:

var_dump($query);

and when I submit the search from now, I get:

string(225) "SELECT * FROM product WHERE price < '5001' UNION SELECT * FROM product WHERE availability = 'Now' UNION SELECT * FROM product WHERE category = 'Garden' UNION SELECT * FROM product WHERE product name like '%a%'" 
Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\Users\Method\Desktop\FionaProjectMeta\Server\htdocs\PerfectoWebsite\SearchByAllCriteria.php on line 120

Still can't see the error.

[Edit 2]:

Okay, the error is in one/both these two lines:

 UNION SELECT * FROM product WHERE category = '$get_Category'
    UNION SELECT * FROM product WHERE product name like '%$get_name%'";

Just unsure where.

[Edit 3]:

I'm no longer getting an error, once I put 'product name' in quotes my SQL query suddenly works, however It's not doing what it's meant to be doing and limiting the results displayed on the page.

Might be, checked that thread and it seems I may have a syntax error in my query. — Method, Oct 27 '14 at 22:35

slapyo · Accepted Answer · 2014-10-27T23:22:18.917

0

The query is wrong. You should be using UNION. UNION let's you combine the results of multiple SELECT statements together. In this case, instead of running 4 separate queries and getting 4 different result sets back, you can run this one query and get one result set back with all of the records. Rather than do a union, you should just simplify the query. There is no need to use UNION in this case. Just put it all in 1 SELECT query.

UNION will return a distinct result set, so no duplicates. If you want all records, duplicate or not, use UNION ALL.

You also need to wrap the variables in quotes. Take a look at the query you're trying to run. Do var_dump($query); and see what the output is.

$query = "SELECT * FROM product WHERE price $get_function $get_price OR avaliability = '$get_Availability' OR category = '$get_Category' OR product name = '$get_name'";

If you're still not getting results back, you should look into any errors. Look into the output of mysqli_error($connection) after your run the query.

edited Oct 27 '14 at 23:22

answered Oct 27 '14 at 22:32

slapyo

2,984
1
14
23

What's union? I'm new to php. – Method Oct 27 '14 at 22:35
Union is MySQL, not PHP. It allows you to combine the results of multiple select statements together. [UNION Syntax](http://dev.mysql.com/doc/refman/5.0/en/union.html) – slapyo Oct 27 '14 at 22:37
I tried using this and still getting a syntax error, going to try and switch my current statement out with a dummy statement just in case I made an error. [EDIT]: I just used a dummy statement and it worked successfully, so it's my statement that's the issue. – Method Oct 27 '14 at 22:39
Updated initial post. – Method Oct 27 '14 at 23:01
Check my answer, updated the query to make it simple. Just 1 SELECT query. What's the output of the new query. – slapyo Oct 27 '14 at 23:02
@Method what is the product name column? That's the error. Does it have a space in it? – slapyo Oct 27 '14 at 23:04
I recently just changed the product name so it's in quotes, and now it isn't throwing an error, but it doesn't seem to output the results of the query correctly. It now seems to work, all of the items in my database are being constructed like they should, no errors, but the search in general doesn't work. – Method Oct 27 '14 at 23:12
Is the column name "product name" with a space? If so, use back ticks to wrap it in the query like this `\`product name\`` – slapyo Oct 27 '14 at 23:15
I did so, it's working :D kinda a stupid error on my part, but now the search functionality isn't working the way it's meant too for some reason. But I guess my error is resolved. – Method Oct 27 '14 at 23:16
Might look into dumping the output of `mysqli_error($connection)` after the query and see if it says anything. – slapyo Oct 27 '14 at 23:23
How do I see the output? mysqli_error($connection);? And it's really strange, it's displaying 15/16 of the rows in product no matter which conditions I set. – Method Oct 27 '14 at 23:46
`var_dump(mysqli_error($connection));` You problem comes down to the query itself. Since it's a simple query with multiple conditions just remove all of the conditions but 1, then run it. See what the results are. Then add one more condition back on and try again. Keep doing that till you've got all the conditions in there. – slapyo Oct 27 '14 at 23:49
I've ran all the conditions individually, and on their own they do exactly what I expect them too, name finds by name, availability shows avaliable items, etc. However, the second I put them together they lead to unexpected and incorrect results. – Method Oct 28 '14 at 00:04
I had to tweak your solution a little, but I think I've finally got it working, I switched the 'OR' for 'AND' and did a few corrections based on previous feedback, and I believe it works. Thanks so much for your help, I appreciate it. Learnt a lot. – Method Oct 28 '14 at 00:13

PHP Complex search

1 Answers1