Mysql query works in Phpmyadmin but not works in PHP

Question

i have two tables the first one is cont_details

     -------------- --------------
    | cont_details_id  | cont_no |
     -----------------------------
    |  1                |   A001 |
    |  2                |   A002 |
    |  3                |   A003 |
    ------------------------------

and the second one is cont_sold_tmp

     --------------
    | id | cont_no |
     --------------
    |  1 |   1    |
    |  2 |   3    |
    ----------------

now i need to select from table cont_details table which are not in temp_cont

so i used this query

select * from cont_details left join cont_sold_tmp on cont_details.cont_details_id = cont_sold_tmp.cont_no where cont_sold_tmp.cont_no is null

This query works perfectly when i check in PhpmyAdmin but when i use it in HTMl, not working

<select name="cont_no"  id="cont_no" data-rel="chosen">
    <option value="">Select</option>
    <?php
       $qry = mysql_query("select * from cont_details left join cont_sold_tmp on cont_details.cont_details_id = cont_sold_tmp.cont_no where cont_sold_tmp.cont_no is null");
           while($res = mysql_fetch_array($qry))
              {
         ?>
         <option value="<?php echo $res["cont_details_id"];?>">
           <?php echo $res["cont_no"];?>
      </option>
     <?php } ?>
    </select>

Database connections are working perfectly, instead of join query i just checked with the simple query "select * from cont_details" its shows all the records. but when i use this join query. i dont get any records in dropdown

Maybe phpmyadmin and your php script use a different user with different permissions? — MrTux, Feb 09 '15 at 13:22
In what way is it "not working"? Can you provide us with an error message? — Mawg says reinstate Monica, Feb 09 '15 at 13:22
You're not doing any error checking at all and it appears as though you're not even connecting to the database. Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement)** instead, and **use [PDO](http://us1.php.net/pdo). — Jay Blanchard, Feb 09 '15 at 13:23
Another PHP coder using `mysql_*` , you may goto hell because of Not securing PHP application ! :) — Pratik Joshi, Feb 09 '15 at 13:26
Database connections are working perfectly, instead of join query i just checked with this query "select * from cont_details" its shows all the records. but when i use this join query. i dont get any records — , Feb 09 '15 at 13:26

Pratik Joshi · Answer 1 · 2015-02-09T13:36:34.683

1

Use

while ($row = mysql_fetch_array($qry, MYSQL_ASSOC)) {

edited Feb 09 '15 at 13:36

answered Feb 09 '15 at 13:24

Pratik Joshi

11,485
7
41
73

1

User print_r($res) in ` while($res = mysql_fetch_array($qry)) {print_r($res)` AND in Inspect element , check what is appearing. – Pratik Joshi Feb 09 '15 at 14:11
Array ( [0] => 1000 [cont_price] => 1000 [1] => 3 [cont_details_id] => 3 [2] => A003 [cont_no] => [3] => 45 [cont_size] => 45 [4] => HC [cont_type] => HC [5] => 30480 Kgs [cont_tonnage] => 30480 Kgs [6] => 2013 [yom] => 2013 [7] => CARGO WORTHY [cont_condition] => CARGO WORTHY [8] => 1 [cont_buying_id] => 1 [9] => 70 [exc_rate] => 70 [10] => [cont_sold_tmp_id] => [11] => [12] => [price] => [13] => [cont_selling_id] => ) yaa its getting value but when i use the query inside dropdown returns nothing – Feb 10 '15 at 06:19
1

See ` [cont_no] => ` You get Blank value for cont_no . Getting ? – Pratik Joshi Feb 10 '15 at 06:38
cont_details.cont_details_id as cont_details_id, cont_details.cont_no as cont_no – Feb 10 '15 at 06:45
1

@Vijay , nice , you can accept answer and upvote as i helped you in debugging. – Pratik Joshi Feb 10 '15 at 06:47

score 1 · Accepted Answer · answered Feb 10 '15 at 06:44

And finally found the mistake what i did -_-

select cont_details.cont_details_id as cont_details_id, 
cont_details.cont_no as cont_no
from cont_details left join cont_sold_tmp
on cont_details.cont_details_id = cont_sold_tmp.cont_no
where cont_sold_tmp.cont_no is NULL

after combining tables, i didn't choose column correctly with table name (.)

so the result when i use <option value="<?php echo $res["cont_details_id"];?>"> , it doesnt exits there so its show empty

thank you for ur replys :-)

Priyank · Answer 3 · 2015-02-09T13:29:59.963

0

try like this,use "IS NULL" instead of "is null":

$qry = mysql_query("select * from cont_details left join cont_sold_tmp on cont_details.cont_details_id = cont_sold_tmp.cont_no where cont_sold_tmp.cont_no IS NULL");

edited Feb 09 '15 at 13:29

answered Feb 09 '15 at 13:24

Priyank

3,778
3
29
48

1

Sorry to be off-topic but which university should i join in India for web developing and programming – Utkarsh Dixit Feb 09 '15 at 13:35
@jQuery.PHP.Magento.com But is there any scope in India related to PHP programming – Utkarsh Dixit Feb 09 '15 at 13:41
@Hudixt , please Visit my profile and mail me , I can answer your question there as This section diverts from Question.Thanks – Pratik Joshi Feb 09 '15 at 13:43

Mysql query works in Phpmyadmin but not works in PHP

3 Answers3

Linked