homepage Welcome to WebmasterWorld Guest from 54.227.41.242
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Help with MySQL syntax error "Resource id #7"
ro1960




msg:4439943
 9:37 am on Apr 12, 2012 (gmt 0)

Hi, I am stuck with a SQL syntax error that I can't identify:
Couldn't execute the query query
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Resource id #7' at line 1.


What is "Resource id #7"?

I only get the error when I run the query in my PHP script.The same query ran in phpMyadmin doesn't give the error.

Here's my PHP code;
[PHP]$date_start_select = $_GET['date_start_select'];
$date_end_select = $_GET['date_end_select'];
$query = mysql_query("
SELECT `id`, `date_start`, `date_end`, `title`, `theme`, `body`
FROM `moca_daysahead`
ORDER BY date_start");

$result = mysql_query($query)
or die ("Couldn't execute the query <i>query</i><br />".mysql_error().".");
echo "Query: ".$query."<br /><br />";


// initialize header and set it to an empty string
$header = '';
// mysql_fetch_assoc will return each row from your result set
// when there are no more results, the while loop ends
while ($row=mysql_fetch_assoc($query))
{

// we only want to print the header if it hasn't been seen in the result yet
if ($header!=$row['date_start'])
{
print "<div class=\"header\" style=\"margin-top:20px\"><u>".$row['date_start']."</u></div><br />\n";
// means the current header display is for $row['date_start']
$header = $row['date_start'];
}
$row['h_title'] = utf8_decode($row['title']);
$row['h_theme'] = utf8_decode($row['theme']);
print "<div style=\"margin-bottom:8px\">";

print "<b><a href=\"daysahead.php?p=daysahead_edit.php&amp;id=".$row['id']."\">".$row['h_title']."</a></b><br />";

if ($row['theme'])
{
print " (".$row['h_theme'].")";
}
print "</div>\n";
}
[/PHP]

MySQL 5.1.49
PHP 4.4.9

Thanks for any help.

 

rocknbil




msg:4440065
 3:55 pm on Apr 12, 2012 (gmt 0)

Heh. A mistake I think we've all made. This is actually a PHP error, one you can identify by the error mySQL is kicking. :-) The "Resource id #7" is the handle PHP has assigned to your actual SQL query object, which you've stored in $query.
Then you execute mysql_query on that object that has already been executed and stored in Resource id #7. It should go like this:

Create a select statement and optionally store it in a variable.

Execute the query in that statement using mysql_query (statement) and store the result in an object.

Execute row queries on that object using one of the fetch_array or fetch_row methods, which returns two PHP arrays*, an indexed array and associative array.

$query = "select statement"
$result = mysql_query($query)

while (or "if", if you only expect a single result, as in selecting by id) $row = mysql_fetch_array($result)

Operate on the fetched array, row by row.

All together, try it like so:

$query = "SELECT `id`, `date_start`, `date_end`, `title`, `theme`, `body`
FROM `moca_daysahead`
ORDER BY date_start";
$result = mysql_query($query) or die("Cannot get results " . mysql_error()); // Always error trap!

while ($row = mysql_fetch_array($result)) {
// * Note that mysql_fetch_array returns TWO arrays here.
// $row['date_start'] and $row[1] are IDENTICAL RESULTS
// etc, your other code
print "<b><a href=\"daysahead.php?p=daysahead_edit.php&amp;id=".$row['id']."\">".$row['h_title']."</a></b><br />";
}

Notable to mention, you can also easily add your input variables in a where statement. It looks like you're going through the entire result set and only printing if it's in your selected range. This is highly inefficient and as your database grows is likely to give you out of memory errors. If you're on shared hosting, you may have your host shut you down for too much memory usage. Only select what you need, like this:

// Cleanse it! remove anything not a digit or -, and execute PHP's escape on it
$date_start_select = mysql_real_escape_string(preg_replace('/[^\d\-]/','',$_GET['date_start_select']));
$date_end_select = mysql_real_escape_string(preg_replace('/[^\d\-]/','',$_GET['date_end_select']));

$query = "SELECT `id`, `date_start`, `date_end`, `title`, `theme`, `body`
FROM `moca_daysahead`
where date_start >= '$date_start_select' and date_start <= '$date_end_select'
ORDER BY date_start";

Many prefer to use between instead, I like the greater than/less than approach (because I can tweak it to just greater or greater+equal, etc.). Same thing, really.

ro1960




msg:4440666
 10:12 pm on Apr 13, 2012 (gmt 0)

Thanks rocknbil for your extended explanation. I planned on adding the WHERE statement next. I did and it works.

I must admit that I am an occasional coder, so most code I use is from cloning code I have used before and I modify it to suit my needs. Most of the time I get by with this method, but sometimes I get stuck although everything looks fine (to me). So I appreciate help from senior coders to help debug.

Then I can maybe submit another deadend I'm facing. A form used to edit data, then to post to the DB, simple. I used this before and cloned the two files, modified them and I get a blank page when posting to the DB.

Here's the edit form code:

<?php
// Obtenir la date et l'heure au moment du chargement de la page
// pour pr&eacute;remplir les champs date_start et time_start
$today = date("Ymd");
$now = date("Hi");

$item_id = $_GET['item_id'];


$query_prev = "SELECT
id AS item_id, title, theme, body, category, coverage, statut,
DATE_FORMAT(date_start, '%Y%m%d') AS Fdate_start,
DATE_FORMAT(date_end, '%Y%m%d') AS Fdate_end,
TIME_FORMAT(date_start, '%H%i') AS Ftime_start,
TIME_FORMAT(date_end, '%H%i') AS Ftime_end
FROM moca_daysahead
WHERE id = '$item_id'
";

$result_prev = mysql_query($query_prev)
or die ("Couldn't execute query <i>query_prev</i><br />".mysql_error().".");
// echo "Query: ".$query_prev."<br /><br />";

while ($row_prev = mysql_fetch_array($result_prev))
{
extract($row_prev);
?>

<!-- START EDIT FORM -->
<div class="tdborder1px">

<form action="daysahead.php?daysahead_edited.php" method="post">
<input type="text" name="item_id" size="50" value="<?php echo $item_id; ?>" hidden>
<table border="0" cellspacing="2" cellpadding="4" width="100%">
<tr>
<td valign="middle" align="center" class="header_plus_brown">
Pr&eacute;vision - &Eacute;dition
</td>
</tr>
</table>


<table border="0" cellspacing="2" cellpadding="4" width="100%">

<tr bgcolor="#cccccc">
<td valign="top" colspan="4">
<b>Titre :</b>
</td>
</tr>

<tr bgcolor="#eeeeee">
<td valign="top" colspan="4">
<?php
echo "<input type=\"text\" name=\"title\" size=\"50\" value=\"$title\">";
?>
</td>
</tr>


<tr bgcolor="#cccccc">
<td valign="top" colspan="2">
<b>Theme :</b>
</td>

<td valign="top" colspan="2">
<b>Categorie :</b>
</td>
</tr>

<tr bgcolor="#eeeeee">
<td valign="top" colspan="2">
<?php
echo "<input type=\"text\" name=\"theme\" size=\"30\" value=\"$theme\">";
?>
</td>

<td valign="top" colspan="2">
<?php
echo "<input type=\"text\" name=\"category\" size=\"30\" value=\"$category\">";
?>
</td>
</tr>



<tr bgcolor="#cccccc">
<td valign="top" align="left">
<b>Date d&eacute;but :</b> AAAAMMJJ
</td>

<td valign="top" align="left">
<b>Heure d&eacute;but :</b> HHMM
</td>

<td valign="top" align="left">
<b>Date fin :</b> AAAAMMJJ
</td>

<td valign="top" align="left">
<b>Heure fin :</b> HHMM
</td>
</tr>


<tr bgcolor="#eeeeee">
<td valign="top" align="left">
<?php
echo "<input name=\"date_start\" type=\"text\" size=\"8\" maxlength=\"8\" value=\"$Fdate_start\" />";
?>
</td>

<td valign="top" align="left">
<?php
echo "<input name=\"time_start\" type=\"text\" size=\"4\" maxlength=\"4\" value=\"$Ftime_start\" />";
?>
</td>

<td valign="top" align="left">
<?php
echo "<input name=\"date_end\" type=\"text\" size=\"8\" maxlength=\"8\" value=\"$date_end\" />";
?>
</td>

<td valign="top" align="left">
<?php
echo "<input name=\"time_end\" type=\"text\" size=\"4\" maxlength=\"4\" value=\"$time_end\" />";
?>
</td>
</tr>


<tr bgcolor="#cccccc">
<td valign="top" colspan="2">
<b>Texte :</b><br /><textarea name="body" rows="10" cols="50"><?php echo $body; ?></textarea>
</td>

<td valign="top" colspan="2">
<b>Couverture :</b><br /><textarea name="coverage" rows="10" cols="50"><?php echo $coverage; ?></textarea>
</td>
</tr>

<tr>
<td valign="top" colspan="2">
<b>Priorit&eacute; :</b><br />
<select name="priority">
<?php
if ($priority == '1')
{
echo "<option label=\"1\" value=\"1\" selected>1</option>";
}
else
{
echo "<option label=\"1\" value=\"1\">2</option>";
}

if ($priority == '2')
{
echo "<option label=\"2\" value=\"2\" selected>2</option>";
}
else
{
echo "<option label=\"2\" value=\"2\">2</option>";
}

if ($priority == '3')
{
echo "<option label=\"3\" value=\"3\" selected>3</option>";
}
else
{
echo "<option label=\"3\" value=\"3\">3</option>";
}
?>
</select>
</td>


<td valign="top" colspan="2">
<b>Statut :</b><br />
<?php
if ($statut == '0')
{
echo "<img src=\"images/square_red.jpg\" alt=\"red\" width=\"10\" height=\"10\" hspace=\"3\" vspace=\"3\" border=\"0\" align=\"left\" /> <input name=\"statut\" type=\"radio\" value=\"0\" checked> Brouillon<br />";
}
else
{
echo "<img src=\"images/square_white.jpg\" alt=\"white\" width=\"10\" height=\"10\" hspace=\"3\" vspace=\"3\" border=\"0\" align=\"left\" /> <input name=\"statut\" type=\"radio\" value=\"0\"> Brouillon<br />";
}

if ($statut == '1')
{
echo "<img src=\"images/square_orange.jpg\" alt=\"orange\" width=\"10\" height=\"10\" hspace=\"3\" vspace=\"3\" border=\"0\" align=\"left\" /> <input name=\"statut\" type=\"radio\" value=\"1\" checked> Validation<br />";
}
else
{
echo "<img src=\"images/square_white.jpg\" alt=\"white\" width=\"10\" height=\"10\" hspace=\"3\" vspace=\"3\" border=\"0\" align=\"left\" /> <input name=\"statut\" type=\"radio\" value=\"1\"> Validation<br />";
}

if ($statut == '2')
{
echo "<img src=\"images/square_green.jpg\" alt=\"orange\" width=\"10\" height=\"10\" hspace=\"3\" vspace=\"3\" border=\"0\" align=\"left\" /> <input name=\"statut\" type=\"radio\" value=\"2\" checked> Valid&eacute;<br />";
}
else
{
echo "<img src=\"images/square_white.jpg\" alt=\"white\" width=\"10\" height=\"10\" hspace=\"3\" vspace=\"3\" border=\"0\" align=\"left\" /> <input name=\"statut\" type=\"radio\" value=\"2\"> Valid&eacute;<br />";
}
?>

</td>
</tr>

<tr>
<td bgcolor="#aaaaaa" valign="middle" colspan="4" align="center">
<input type="submit" value="Go">
</td>
</tr>
</table>
</form>
<!-- END EDIT FORM -->

</div>
<?php
} // while
?>


and here is the post to DB code:

<?php

// GETTING FORM DATA
$item_id= $_POST['item_id'];
$date_modified = date("YmdHis");
$date_start= $_POST['date_start'];
$date_end= $_POST['date_end'];
$time_start= $_POST['time_start'];
$time_end= $_POST['time_end'];
$title= $_POST['title'];
$theme= $_POST['theme'];
$body= $_POST['body'];
$category= $_POST['category'];
$coverage= $_POST['coverage'];
$statut= $_POST['statut'];

// CONVERTING HTML ENTITIES
$title= htmlentities($title, ENT_QUOTES, "UTF-8");
$theme= htmlentities($theme, ENT_QUOTES, "UTF-8");
$body= htmlentities($body, ENT_QUOTES, "UTF-8");
$category= htmlentities($category, ENT_QUOTES, "UTF-8");
$coverage= htmlentities($coverage, ENT_QUOTES, "UTF-8");


// INSERTING INTO DB
$sql="
UPDATE moca_daysahead
SET
date_modified = '$date_modified',
date_start = '$date_start',
date_end = '$date_end',
time_start = '$time_start',
time_end = '$time_end',
title = '$title',
theme = '$theme',
body = '$body',
category = '$category',
coverage = '$coverage',
statut = '$statut'
WHERE id = '$item_id'
";
// run SQL against the DB
echo "<br />".$sql."<br />";
$result = mysql_query($sql);
/////////////////////////////

?>

<p class="header_plus">
Votre message a été &eacute;dit&eacute;.
</p>

<p class="header_plus">
Merci.
</p>


System
redhat



msg:4440838
 10:38 am on Apr 14, 2012 (gmt 0)

The following message was cut out to new thread by eelixduppy. New thread at: databases_sql_mysql/4440836.htm [webmasterworld.com]
7:50 am on Apr 14, 2012 (est -5)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved