PHP syntax wrong? Outputting Resource id #3

Question

<?php
$link = mysql_connect('localhost', 'user', 'password');
if (!$link) {
die('Failed to connect to MySQL: ' . mysql_error());
}
$db_selected = mysql_select_db('mysql', $link);
if (!$db_selected) {
  die ("Can\'t use db : " . mysql_error());
}
$query = sprintf("SELECT church_id FROM hours 
         WHERE day_of_week = DATE_FORMAT(NOW(), '%w') AND 
         CURTIME() BETWEEN open_time AND close_time",
    mysql_real_escape_string($day_of_week),
    mysql_real_escape_string($open_time),
    mysql_real_escape_string($close_time));
$result = mysql_query($query);

if (!$result) {
    $message = 'Invalid query: ' .mysql_error() . "\n";
    $message .= 'Whole query: ' .$query;
    die($message);
}

while ($row = mysql_fetch_array($result)) {
    echo $row['shop_id'];
}

mysql_free_result($result);
echo "end";
?>

I know SQL query works by copying/pasting into phpmyadmin. I want the script to just output a shop_id or series of shop_ids. Right now it outputs Resource id #3. I looked up how to fix it and mysql_fetch_array was supposed to be the answer. What am I doing wrong?

you should post what you are seeing that looks wrong -- crash result, output, etc. Also please note **mysql is deprecated and new code should be written to mysqli** — RobP, Apr 15 '14 at 03:06
... or PDO. See [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — Phil, Apr 15 '14 at 03:07
1) Where does `$day_of_week` come from? 2) Your `sprintf` string has only one placeholder yet you are passing three values? 3) You `SELECT church_id` yet try to echo `shop_id` 4) That output is not possible from the code above. At no point are you displaying anything that could be a resource — Phil, Apr 15 '14 at 03:09
Resource id #3 means your query is correct. It's just that your query is lacking... the shop_id row. :) — witherwind, Apr 15 '14 at 03:12

score 1 · Answer 1 · answered Apr 15 '14 at 03:11

1

I'm looking over your query and I only see you selecting church_id and you want to output shop_id, you should include that in your select like so:

$query = sprintf("SELECT church_id, shop_id FROM hours WHERE day_of_week = DATE_FORMAT(NOW(), '%w') AND CURTIME() BETWEEN open_time AND close_time",
    mysql_real_escape_string($day_of_week),
    mysql_real_escape_string($open_time),
    mysql_real_escape_string($close_time));
$result = mysql_query($query);

answered Apr 15 '14 at 03:11

Skewled

783
4
12

There are still 3 parameters being passed to sprintf and only one placeholder. This wouldn't cause it to fail, but it's still an error in the code you have in your answer. – Mike Apr 15 '14 at 03:16

score 0 · Answer 2 · answered Apr 15 '14 at 03:22

You have several problems here, the first of which is that you are using the mysql extension which is unmaintained and officially deprecated (due to be removed). I suggest you try mysqli...

$link = new mysqli('localhost', 'user', 'password', 'mysql');
if ($link->errno) {
    throw new Exception($link->error, $link->errno);
}

Whilst you've done a commendable job of securing your query, you really should be using the better tools available in mysqli, notably prepared statements...

$stmt = $link->prepare('SELECT church_id FROM hours 
     WHERE day_of_week = DATE_FORMAT(NOW(), ?) AND 
     CURTIME() BETWEEN open_time AND close_time');
if (!$stmt) {
    throw new Exception($link->error, $link->errno);
}
$stmt->bindParam('s', $day_of_week); // assuming $day_of_week is properly defined
if (!$stmt->execute()) {
    throw new Exception($stmt->error, $stmt->errno);
}

Fetching data from a mysqli prepared statement is a little different to the old mysql_fetch_array however it's not difficult. One way is to use result binding

$stmt->bind_result($church_id);
while ($stmt->fetch()) {
    echo $church_id;
}

PHP syntax wrong? Outputting Resource id #3

2 Answers2