Cannot get result when passing php variable to mysql query

Question

I am trying to pass a part of a file name as a parameter in the my sql query in php. My file name is in the form db_feature_T1..iOXXdrXXcAMEra_092521.txt from which I want the part except the db_feature and .txt i.e. T1..iOXXdrXXcAMEra_092521

I want the img_id from the database whose img_path=dressimages/T1..iOXXdrXXcAMEra_092521.jpg

My code is as follows.I was able to seperate the file name but I cannot get any result from the database.

            <?php
    include('db.php');

    if ($handle = opendir('C:\Users\adithi.a\Desktop\db_features')) {



        while (false !== ($entry = readdir($handle))) {
            $entry=substr($entry,11,-4); //this seperates the file name db_feature_T1..iOXXdrXXcAMEra_092521.txt to T1..iOXXdrXXcAMEra_092521



            $image_path='dressimages/'.$entry.'.jpg';//I want to pass the img_path as it is saved in the database in the form of dressimages/T1..iOXXdrXXcAMEra_092521.jpg


         $result=  mysql_query("select img_id from tbl_image where img_path='$image_path'") or die("Sorry");
          echo $result;//I dont get anything as output. 
        }

    }

        closedir($handle);
    ?>

I went to an infinite loop when executing the code above so i tried:

    $image_path='dressimages/'.$entry.'.jpg';//I want to pass the img_path as it is saved in the database in the form of dressimages/T1..iOXXdrXXcAMEra_092521.jpg

        $sql = "select img_id from tbl_image where img_path=".$image_path;
     echo $sql . '<br />';
        $result=mysql_query("$sql");

       }

            while($data=mysql_fetch_assoc($result))
                  {

                   echo $data["img_id"];
                   }

and Now i get the error mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\wamp\www\FashionBonanza\Readfile.php on line 34

Any help

Try doing this.. $result= mysql_query("select img_id from tbl_image where img_path='" . $image_path . "'") or die("Sorry"); Just let me know what happens.. — Hiren Pandya
– Hiren Pandya, Commented Apr 11, 2013 at 5:04
didn't work the page keeps on loading as it went to some infinite loop — user1583647
– user1583647, Commented Apr 11, 2013 at 5:08

Hanky Panky · Accepted Answer · 2013-04-11 05:06:05Z

4

You have to first fetch the data from your result

$data=mysql_fetch_assoc($result);  // this is missing
print_r($data); // or 
echo $data["img_id"];

and in case there can be multiple results you can do so in a loop

while($data=mysql_fetch_assoc($result))
{
print_r($data); // or 
echo $data["img_id"];
}

answered Apr 11, 2013 at 5:06

Hanky Panky

46.9k9 gold badges76 silver badges95 bronze badges

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

2 Comments

user1583647 Over a year ago

I tried this too. It doesn't work. The page is loading as it has gone in some infinite loop.

bestprogrammerintheworld Over a year ago

@user1583647 - I can assure you it has nothing to do with this code.

bestprogrammerintheworld · Accepted Answer · 2013-04-11 07:36:48Z

1

Because you're dealing with so many files I would try another approach. That would be to store all paths into an array, make a single query and associate id's with path's. I'm not sure this code will work (I haven't tested it), but I hope you get the picture:

<?php
include('db.php');

if ($handle = opendir('C:\Users\adithi.a\Desktop\db_features')) {

//Store all paths into $image_paths array
$image_paths = array();
while (false !== ($entry = readdir($handle))) {
    $entry=substr($entry,11,-4); //this seperates the file name db_feature_T1..iOXXdrXXcAMEra_092521.txt to T1..iOXXdrXXcAMEra_092521
    if (strlen($entry) !== 0) {
    $image_paths[]='dressimages/'.$entry.'.jpg';//I want to pass the img_path as it is saved in the database in the form of dressimages/T1..iOXXdrXXcAMEra_092521}
    }
}
closedir($handle);

//Implode paths
$pathQuotesArray = array_map('apply_quotes', $image_paths); //Looks like 'filename1', 'filename2' etc
$pathQuotes = implode(',', $pathQuotesArray); 

//Do one query 
$result=  mysql_query("select img_id from tbl_image WHERE img_path IN ($pathQuotes)") or die(mysql_error());

//Associate id's with paths
while($data=mysql_fetch_assoc($result))
{
    $key[$data["img_id"]] = $data["img_path"];
}

echo $key[5]; //If img_id is 5, then it would show correct path (hopefully :-))

function apply_quotes($item)
{
    return "'" . mysql_real_escape_string($item) . "'";
}



?>

edited Apr 11, 2013 at 7:36

answered Apr 11, 2013 at 5:24

bestprogrammerintheworld

5,5299 gold badges48 silver badges76 bronze badges

11 Comments

user1583647 Over a year ago

yes it does when I comment the sql part but if the sql query in uncommented it agains goes to an infinite loop

user1583647 Over a year ago

the output is select img_id from tbl_image where img_path='dressimages/T1..aJXiRiXXborqo7_063510.jpg' select img_id from tbl_image where img_path='dressimages/T1..aQXm0bXXboCaMY_025420.jpg'

user1583647 Over a year ago

and when I tied this $sql = "select img_id from tbl_image where img_path='$image_path'"; echo $sql . '<br />'; $result=mysql_query($sql); while($data=mysql_fetch_assoc($result)) { print_r($data); } } It again went to an infinite loop I dont know what is the problem

bestprogrammerintheworld Over a year ago

hm maybe the dots are causing an issue? Dots are used in regexp in MySQL: dev.mysql.com/doc/refman/5.0/en/regexp.html. If you try to change name of files (so they are without dots: What happens)? Try to change filenames to: T1aQXm0bXXboCaMY_025420.jpg and T1aJXiRiXXborqo7_063510.jpg'

user1583647 Over a year ago

actually I have 30,000 images which has its corresponding text file. i am doing this to save it in the database so I dont think I can change the filename.

|

Hanky Panky · Accepted Answer · 2013-04-11 05:11:01Z

0

Try this

$result=  mysql_query("select img_id from tbl_image where img_path="$image_path) or die("Sorry");

edited Apr 11, 2013 at 5:11

Hanky Panky

46.9k9 gold badges76 silver badges95 bronze badges

answered Apr 11, 2013 at 5:06

Mayur Kukadiya

9946 silver badges20 bronze badges

5 Comments

Ranjith Over a year ago

Your way of assumption its wrong. PHP doesn't parse code without quotes inside the mysql_query()

Stefan Brendle Over a year ago

isn't there at least a point missing to concat the query and the variable? And the single quotes are required, because the path is string, I guess - and it will break the query, especially if there are spaces in the variable ...?

Mayur Kukadiya Over a year ago

u should have to echo query and then direct run to database

user1583647 Over a year ago

sorry I didn't get you.can you give an example

Mayur Kukadiya Over a year ago

echo $sql; then output of this query direct run in mysql

Stefan Brendle · Accepted Answer · 2013-04-11 05:06:53Z

-3

Just two ideas:

$result=  mysql_query("select img_id from tbl_image where img_path='".$image_path."'") or die("Sorry");

Maybe it's better to do it this way. Otherwise set a extra string variable for the SQL-query, so you can track the exact query, which was executed.

$sQry = "select img_id from tbl_image where img_path='".$image_path."'";
var_dump($sQry);
$result=  mysql_query($sQry) or die("Sorry");

So you can check, if the variable contains the correct value or not.

Next point is the "echo". You can't echo a result set, use functions like mysql-fetch-array() or mysql_fetch_assoc() - http://php.net/manual/de/function.mysql-fetch-array.php - or other ones.

while ($row = mysql_fetch_assoc($result)) {
    echo $row["img_id "];
}

answered Apr 11, 2013 at 5:06

Stefan Brendle

1,5646 gold badges20 silver badges39 bronze badges

1 Comment

user1583647 Over a year ago

I tried this too no result when I comment the sql part and echo the image_path it does but when I uncomment it it goes to an infinite loop i.e. the page keeps on loading

Collectives™ on Stack Overflow

Cannot get result when passing php variable to mysql query

4 Answers 4

2 Comments

11 Comments

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

11 Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related