MySQL query works in phpmyadmin, but not through php

Question

Any ideas would help, I was on this for a while and just can not understand what is wrong.

Problem: the query works fine until I add a few words to the search, which you can see in bold below. However, I repeated the sql query and then inserted it into phpmyadmin and it worked fine, but through php it outputs 0 records. This makes no sense, and I cannot figure out what the result 0 might cause.

SELECT DISTINCT c.id FROM carpets AS c INNER JOIN carpet_relations AS r1 ON c.id = r1.carpet_id INNER JOIN carpet_relations AS r2 ON c.id = r2.carpet_id INNER JOIN carpet_relations AS r3 ON c.id = r3.carpet_id WHERE c.active = '1' AND ((c.title LIKE '%north tabriz%') OR **(c.title LIKE '%north%') OR (c.title LIKE '%tabriz%')** OR (c.item_no LIKE '%north tabriz%') OR **(c.item_no LIKE '%north%') OR (c.item_no LIKE '%tabriz%')** OR (c.pattern LIKE '%north tabriz%') OR **(c.pattern LIKE '%north%') OR (c.pattern LIKE '%tabriz%')** OR (c.period LIKE '%north tabriz%') OR **(c.period LIKE '%north%') OR (c.period LIKE '%tabriz%')** OR (c.country LIKE '%north tabriz%') **OR (c.country LIKE '%north%') OR (c.country LIKE '%tabriz%')**) AND (c.width_feet BETWEEN '0' AND '22') AND (c.width_inches BETWEEN '0' AND '11') AND (c.height_feet BETWEEN '0' AND '49') AND (c.height_inches BETWEEN '0' AND '11') ORDER BY c.item_no id int(11) NO PRI NULL auto_increment active int(11) NO NULL title varchar(250) NO NULL item_no varchar(250) NO NULL country varchar(250) NO NULL period varchar(250) NO NULL pattern varchar(250) NO NULL price float NO NULL web_special float NO NULL notes text NO NULL width_feet int(11) NO NULL width_inches int(11) NO NULL height_feet int(11) NO NULL height_inches int(11) NO NULL restrict int(11) NO NULL views_amount int(11) NO NULL last_modified datetime NO NULL modified_by int(11) NO NULL 

Answer 1

Try the following:

 SELECT DISTINCT c.id FROM carpets AS c INNER JOIN carpet_relations AS r1 ON c.id = r1.carpet_id INNER JOIN carpet_relations AS r2 ON c.id = r2.carpet_id INNER JOIN carpet_relations AS r3 ON c.id = r3.carpet_id WHERE c.active = '1' AND ((c.title LIKE '%north%') OR (c.title LIKE '%tabriz%') OR (c.item_no LIKE '%north%') OR (c.item_no LIKE '%tabriz%') OR (c.pattern LIKE '%north%') OR (c.pattern LIKE '%tabriz%') OR (c.period LIKE '%north%') OR (c.period LIKE '%tabriz%') OR (c.country LIKE '%north%') OR (c.country LIKE '%tabriz%')) AND (c.width_feet BETWEEN 0 AND 22) AND (c.width_inches BETWEEN 0 AND 11) AND (c.height_feet BETWEEN 0 AND 49) AND (c.height_inches BETWEEN 0 AND 11) ORDER BY c.item_no 

I dropped the "LIKE"% north tabriz% 'sections because they were redundant, because if it corresponded to the "northern tabriz" alone, then the "north" and "tabriz" would also be true.

In addition, I removed the single quotes that surrounded the numbers in between sentences.

It is not possible to play tables for validation, since you only have one table, but hope this helps.

Answer 2

Pay attention to how you use quotes in PHP. For SQL queries with the where clause, this can be a problem, since the where clause must specify WHERE x = 'value' in single quotes. So make sure your common SQL string uses double quotes. In addition, PHP variables in single quotes will not be evaluated, so if you base the where clause on a PHP variable, you need to wrap this variable in single quotes before using it in an SQL statement. I hope this all made sense, I was stuck on this issue for 2 days when I started learning PHP / MySQL.

Answer 3

first try simple queries (to check the connection) if they do not work, look at the database host, username, password (they may be mistakenly entered)

Once you are confident in connecting db, try using the multiple request function in php, remove the unnecessary distance, run the request in a for loop or several steps.

hope this helps

Answer 4

Most likely, this is not a request, but an error in your PHP code somewhere. Can you post your PHP code so we can take a look?

Answer 5

Sometimes I get this problem, it only happens when there are any problems with the connections or your request. Print your request and see how it prints. In the request, you should use $var .

Answer 6

Try to handle ("") correctly in your php code. If your request works fine in phpMyAdmin, it should also work in your php code. Or some error may occur in connecting to the database. Make sure you select the correct database for the table you want.