CODEWITHSUNDEEP
phpmysqlphpmyadmin
N8imus
N8imus

Reputation: 253

MySQL query works in phpmyadmin but not through php

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

Upvotes: 9

Views: 3104

Answers (6)

Syed Shoaib Abidi
Syed Shoaib Abidi

Reputation: 2366

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.

Upvotes: 0

Max
Max

Reputation: 117

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.

Upvotes: 0

Syed Irfan Ahmed
Syed Irfan Ahmed

Reputation: 31

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

Upvotes: 1

user569143
user569143

Reputation:

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.

Upvotes: 3

Timothy Martens
Timothy Martens

Reputation: 688

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?

Upvotes: 0

James
James

Reputation: 3805

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.

Upvotes: 2

Related Questions