PHP, MySQL if, else conditional is not working

Question

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;

LIKE is exactly what should be applied here. Other than that, you have deviated from is considered standard MySQLi syntax which is going to lead to many other problems here. — Jay Blanchard
– Jay Blanchard, Commented Aug 6, 2015 at 12:31
wouldn't you want to test both, to prevent doing ->EOF on a boolean? — Sumurai8
– Sumurai8, Commented Aug 6, 2015 at 12:41
According to phplens.com/adodb/reference.functions.execute.html, you should have passed an array with one element as the second argument to $conn->Execute(), like array($description). — Mikhail Batcer
– Mikhail Batcer, Commented Aug 6, 2015 at 13:21

ImHigh · Accepted Answer · 2015-08-06 12:48:01Z

1

$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..

answered Aug 6, 2015 at 12:48

ImHigh

1911 silver badge5 bronze badges

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

4 Comments

Jay Blanchard Over a year ago

What about prepared statements? It looks like the OP wanted to use those.

Mikhail Batcer Over a year ago

@JayBlanchard I've made an edit to ImHigh's answer addressing this.

Jay Blanchard Over a year ago

You are, in effect, changing the poster's answer which may not be the intent of the poster. You may want to add your own answer @MikhailBatcer

Mikhail Batcer Over a year ago

@JayBlanchard Well, I'm sorry, I thought it's OK. Posted as my own answer.

UnderPhp · Accepted Answer · 2015-08-06 12:59:17Z

0

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,

answered Aug 6, 2015 at 12:59

UnderPhp

3881 gold badge3 silver badges14 bronze badges

1 Comment

Philipp Over a year ago

No, varchar can be bigger than 250 bytes. It can take up to 65,535 charakters. The limitation is the amount of bytes for the total row as all the columns share those 65,535 byte, text-types do not have those limits as they are saved in a different way.

Mikhail Batcer · Accepted Answer · 2015-08-06 13:38:52Z

0

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));

answered Aug 6, 2015 at 13:38

Mikhail Batcer

2,1059 gold badges39 silver badges60 bronze badges

Collectives™ on Stack Overflow

PHP, MySQL if, else conditional is not working

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related