CODEWITHSUNDEEP
phpmysql
Anay Bose
Anay Bose

Reputation: 890

PHP, MySQL if, else conditional is not working

I am struck while fetching data from MySQL table. I am using a conditional WHERE clause to filter out results. I have two tables whose schemas are:

CREATE TABLE IF NOT EXISTS `images` (
  `image_id` int(11) unsigned NOT NULL,
  `keyword_id` int(11) unsigned NOT NULL,
  `url` varchar(2083) NOT NULL,
  `size` varchar(50) NOT NULL,
  `color` varchar(50) NOT NULL,
  `type` varchar(50) NOT NULL,
  `created_at` datetime NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=901 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `keywords` (
  `id` int(11) unsigned NOT NULL,
  `description` varchar(100) NOT NULL,
  `created_at` datetime NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

I have saved some keywords and image links. To normalize data, I have saved image links in a separate 'images' table where 'keyword_id' is the foreign key that corresponds to 'description' (i.e. keywords) field in keywords table. With the following SQL syntax I have tried to specify a selection criteria to pull image links by keyword like "Cholesterol levels." Now, when the selection criteria fails, the script must output some errors, which is not happening with my If conditional statement I have written, and I cannot figure out why? This is my first concern.

Secondly, I would like to fetch images by not an exact keywords match. For example, if my keywords table contain a keyword "Cholesterol levels," my script can successfully fetch images by that keyword; however, fails to execute when I search only by "Cholesterol" and/or "levels". I know Mysql's LIKE operator, but I am not sure if it can be applied here.

 $dbdriver = "mysqli";
 $conn     = ADONewConnection($dbdriver);
 $conn->Connect($server, $user, $password, $database);
 $description = "cholesterol levels"; 
 $brecordSet = $conn->Execute('
     SELECT * FROM keywords 
     INNER JOIN images 
     WHERE description = ? AND keywords.id = images.keyword_id 
     ORDER BY RAND() 
     LIMIT 1
 ', $description);


 if (!$brecordSet)
   {

  print $conn->ErrorMsg();
  // Optionally
  print "Echo on failure";
   }
 else
   {
      $url  = $brecordSet->fields['url']; 
   }

   echo $url;

Upvotes: 0

Views: 134

Answers (3)

Mikhail Batcer
Mikhail Batcer

Reputation: 2065

Using prepared statement.

$description = "%cholesterol levels%"; 
$brecordSet = $conn->Execute("
    SELECT * FROM keywords 
    INNER JOIN images 
    WHERE description LIKE :description 
      AND keywords.id = images.keyword_id 
    ORDER BY keywords.description ASC
    ", array('description' => $description));

Upvotes: 0

UnderPhp
UnderPhp

Reputation: 368

Sorry, I can't commect.. You have problem in

This line:

  `url` varchar(2083) NOT NULL,

The limit of varchar is 250 bytes. You can change the Type to:

  `url` text(2083) NOT NULL,

Upvotes: 0

ImHigh
ImHigh

Reputation: 191

$description = "cholesterol levels"; 
$brecordSet = $conn->Execute("SELECT * FROM keywords INNER JOIN images WHERE description LIKE '%".$description."%' AND keywords.id = images.keyword_id ORDER BY keywords.description ASC");

Using the above query, you will get records in an array format that is stored in $brecordset variable. Then using a foreach loop , you can loop through each record and access it..Hope this helps you..

Upvotes: 1

Related Questions