MySQL query works in phpmyadmin but not through php

Question

Any ideas would help, I have been on this for a while now and just can't figure out what is wrong.

Problem: query works fine until I added in the multiple word search which you can see bolded below. However, I echoed the sql query and then pasted in phpmyadmin and it worked perfectly but through the php it results with 0 records. It doesn't make any sense and I can't figure out what could be causing a result of 0.

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

Does any other easier statement work in your context? (Like SELECT * FROM carpets) — djot
– djot, Commented Dec 16, 2011 at 15:53
It would be helpful to see your table schemas and the error from mysql. — Brian
– Brian, Commented Dec 16, 2011 at 15:54
yes they do as well as if the areas that are surrounded by ** are removed then it works just fine on the front end....however when i add those sections in there on the front end it does not work and on the backend (phpmyadmin) it does — N8imus
– N8imus, Commented Dec 16, 2011 at 15:56
If the same query returns different results from different context, my first check would be if I really run it against the same database? — codeling
– codeling, Commented Dec 16, 2011 at 15:56
have you tried the MATCH ... AGAINST for your query instead? — Dany Khalife
– Dany Khalife, Commented Dec 16, 2011 at 16:00

user569143 · Accepted Answer · 2012-01-04 03:39:28Z

3

Pay close attention to how you use quotes in PHP also. For SQL queries with a where clause specifically it can be an issue since the where clause need to say WHERE x = 'value' in Single quotes. Therefore make sure your overall SQL string uses Double quotes. Also, PHP variables in single quotes will not be evaluated, so if you are basing a where clause on a PHP variable you need to wrap that variable in Single quotes before using it in the SQL statement. I hope all of that made sense, I got stuck on this issue for 2 days when I started learning PHP/MySQL.

answered Jan 4, 2012 at 3:39

user569143

Sign up to request clarification or add additional context in comments.

Comments

James · Accepted Answer · 2011-12-17 03:26:02Z

Try this:

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, since if it matched "north tabriz" alone, then "north" and "tabriz" would also be true.

Also, I took the single quotes away that were surrounding the numbers in the between clauses.

Can't reproduce the tables to test it since you only have 1 table shown, but hope this helps.

Syed Irfan Ahmed · Accepted Answer · 2012-02-02 09:31:36Z

1

try simple queries first ( to test connection) if they dont work look into database host, username , password (they might be wrongly input)

Once you are sure of the db connection , try using multi query function in php, remove needless spacing , run the query in a for loop or multiple steps

hope this helps

answered Feb 2, 2012 at 9:31

Syed Irfan Ahmed

311 silver badge3 bronze badges

Comments

Timothy Martens · Accepted Answer · 2011-12-21 18:38:10Z

0

It's most likely not the query but an error in your PHP code somewhere. Can you post you PHP code for us to take a look at?

answered Dec 21, 2011 at 18:38

Timothy Martens

6884 silver badges20 bronze badges

Comments

Aliaksei Kliuchnikau · Accepted Answer · 2012-02-15 08:05:28Z

0

Sometimes I get this problem, it happens only when there is any problem with connections or or your query. Please print your query and see how it prints. In query you must be using $var.

edited Feb 15, 2012 at 8:05

Aliaksei Kliuchnikau

13.7k4 gold badges63 silver badges74 bronze badges

answered Feb 15, 2012 at 7:44

Max

1174 silver badges18 bronze badges

Comments

Yaroslav · Accepted Answer · 2012-10-26 06:38:49Z

0

Try to handle ("") correctly in your php code. If your query works fine in phpMyAdmin then it should work with in your php code too. Or there could be some error in your database connection. Make sure you are selecting correct database of your desired table.

edited Oct 26, 2012 at 6:38

Yaroslav

6,55210 gold badges54 silver badges91 bronze badges

answered Feb 28, 2012 at 20:34

Syed Shoaib Abidi

2,37617 silver badges19 bronze badges

Collectives™ on Stack Overflow

MySQL query works in phpmyadmin but not through php

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related