error while running MySQL query

Question

I get the error Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given when i try to use the code below.

include('config.php');

$con = mysql_connect($dbhost,$dbuser,$dbpasswd);
if (!$con)
  {
      die('Could not connect: ' . mysql_error());
  } else {
      mysql_select_db($dbname, $con);
  }
$date = getdate();
$result = mysql_query("SELECT * FROM '" . $table_prefix . "users' where user_regdate = '" . $date . "'");
$total = 0;
while($row = mysql_fetch_array($result))
  {
  $total++;
  echo $row['username'];
  echo "<br />";
  }

I don't think you need the single quotes around the table name. — John Conde, May 03 '12 at 02:05

Michael Berkowski · Answer 1 · 2012-05-03T02:33:09.580

1

You have quoted your table name with single quotes '. If you quote it, do so with backticks. Single quotes are only for string literals in MySQL. This results in a syntax error in your query.

$result = mysql_query("SELECT * FROM `" . $table_prefix . "users` where user_regdate = '" . $date . "'");

Next, getdate() in PHP returns an array rather than the date string, which supplies your query with the word Array when passed as a string to the query. Instead, use MySQL's native date functions to get today's date in the format MySQL expects (yyyy-mm-dd):

Edit after comments:

Since you have a unix timestamp rather than a date column, create a Unix timestamp of the date:

where user_regdate = UNIX_TIMESTAMP(GETDATE())

A little error checking on the mysql_query() call would have revealed the source of the error in the query, and indeed is necessary to prevent your script from failing fatally.

$result = mysql_query("SELECT * FROM `" . $table_prefix . "users` where user_regdate = '" . $date . "'");
if (!$result) {
  // query failed
  echo mysql_error();
}
else {
   // all is well, proceed with fetch...
}

Finally, I note that you're collecting the number of rows into $total++. That is unnecessary (unless you are performing some more specific logic we don't see) because the value is available via mysql_num_rows($result).

edited May 03 '12 at 02:33

answered May 03 '12 at 02:05

Michael Berkowski

260,803
45
432
377

Ok the ` thing got rid of the errors. I have never seen those before. But it is still returning no results. So my guess is that my sql date format is a bunch of numbers. "1283216436". Im guessing that $date = date('Y-m-d'); does not supply these numbers i am needing. – alexander7567 May 03 '12 at 02:26
Ok i attempted this instead to fix this $result = mysql_query("SELECT * FROM `" . $table_prefix . "users` where user_regdate = '" . strtotime($date) . "'");. However i still get a date of 1335931200, when i need 1335973416. – alexander7567 May 03 '12 at 02:30
@alexander7567 Don't use `strtotime()` on the date string. Instead just use `$date = time();` to get a timestamp. Ideally, that column should be changed to hold a proper datetime type instead of a Unix timestamp. – Michael Berkowski May 03 '12 at 02:32
@alexander7567 Actually, I edited above. It is better to use MySQL's own date functions instead of PHP at all for that bit... – Michael Berkowski May 03 '12 at 02:38

score 0 · Answer 2 · answered May 03 '12 at 02:08

0

Your query is erroring out. From http://php.net/manual/en/function.mysql-query.php:

mysql_query returns a resource on success, and false on error.

answered May 03 '12 at 02:08

Cory Carson

266
1
6

Ok, that makes since. No my next question is how do I search for today's date in mysql. – alexander7567 May 03 '12 at 02:17
@alexander7567 for today in mysql you can try `select now()` it will return you today date and time. – Nikson Kanti Paul May 03 '12 at 05:12

score 0 · Answer 3 · answered May 03 '12 at 05:02

Because its returning 0 rows check mysql_num_rows() include('config.php');

$con = mysql_connect($dbhost,$dbuser,$dbpasswd);
if (!$con)
  {
      die('Could not connect: ' . mysql_error());
  } else {
      mysql_select_db($dbname, $con);
  }
$date = getdate();
$result = mysql_query("SELECT * FROM '" . $table_prefix . "users' where user_regdate = '" . $date . "'");
$total = 0;
if(mysql_num_rows($result)){
while($row = mysql_fetch_array($result))
  {
  $total++;
  echo $row['username'];
  echo "<br />";
  }
}

error while running MySQL query

3 Answers3