Mysql Warning Question

**TheMovieman** · July 27, 2005

Hello all again,

I'm working on an Edit/Delete page to manage my actors/movies tables. I got the page and links to work fine but I get a warning...

Here's a copy of the page:

Manage Authors

Ben Affleck Edit Delete

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/moviema/public_html/admin/actors.php on line 32

Add new author

Return to front page

Here's the code:

><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<html>
<head>
<title>Movie Management System: Manage Actors</title>
<meta http-equiv="content-type"
   content="text/html; charset=iso-8859-1" />
</head>
<body>
<h1>Manage Authors</h1>
<ul>
<?php

$dbcnx = @mysql_connect('localhost', 'database_name, 'password');
if (!$dbcnx) {
 exit('<p>Unable to connect to the ' .
     'database server at this time.</p>');
}

if (!@mysql_select_db('database_name')) {
 exit('<p>Unable to locate the movie ' .
     'database at this time.</p>');
}

$actors = @mysql_query('SELECT ActorID, FirstName, LastName FROM actor_table');
if (!$actors) {
 exit('<p>Error retrieving actors from database!<br />'.
     'Error: ' . mysql_error() . '</p>');
}

// HERE'S WHERE THE TROUBLE IS
while ($actors = mysql_fetch_array($actors)) {
// END

 $id = $actors['ActorID'];
 $first_name = htmlspecialchars($actors['FirstName']);
 $last_name = htmlspecialchars($actors['LastName']);
 echo "<li>$first_name $last_name ".
     "<a href='editactor.php?id=$id'>Edit</a> ".
     "<a href='deleteactor.php?id=$id'>Delete</a></li>";
}

?>
</ul>
<p><a href="newauthor.php">Add new author</a></p>
<p><a href="index.html">Return to front page</a></p>
</body>
</html>

I've marked where the trouble is. When fiddling around with that, I actually got the warning removed, but then the script didn't get the actor's first or last name. The edit/delete links were there, however, it was blank after the "id=".

This is probably something really dumb, but I thought maybe somebody can set me right here...

-Brian

Edited July 27, 2005 by TheMovieman

**TweezerMan** · July 28, 2005

When you execute the query:

>$actors = @mysql_query('SELECT ActorID, FirstName, LastName FROM actor_table');

...the result resource is being stored in the $actors variable.

Then, when you try to read result rows from the query:

>while ($actors = mysql_fetch_array($actors)) {

...you're fetching a row from the query and storing it back into the $actors variable. This clobbers the result resource of the query (overwrites it) and you can't fetch any more rows from it.

You need to use a different variable to represent the query result resource:

>$result = @mysql_query('SELECT ActorID, FirstName, LastName FROM actor_table');

>while ($actors = mysql_fetch_array($result)) {

Hope this helps...

**TheMovieman** · July 28, 2005

Yeah, I figured that out, thanks

I do have one more problem. This isn't a big deal as I can use the command line but it would make things quicker. When clicking the "delete" link, it goes through like it has, but it's still in the database...

Here's the code:

>$id = $_GET['id'];
"DELETE FROM actor_table WHERE ActorID='$id'";

I apologize for the questions, but anything I google (or read) doesn't clearly explain how these things work. In this case, I believe the $id variable (the id from the URL) is supposed to store the ActorID and then place it in the DELETE function. By the way, this code is in conjunction with the previous one and is supposed to carry over (the address is something like: http://www.domain.com/folder/deleteactor.php?id=3

What am I missing?

Thanks again for any help

Edited July 28, 2005 by TheMovieman

**TweezerMan** · July 28, 2005

The DELETE statement is a MySQL query, but has to be to be executed on the MySQL server in order for the query action to be carried out. As it is, the query in your code is just a text string that doesn't do anything.

You need to execute the query with mysql_query(), just like you did with your SELECT query:

>$result = @mysql_query("DELETE FROM actor_table WHERE ActorID='$id'");

**TheMovieman** · July 29, 2005

Thanks David, I think I'm getting a hang on this.

I do have one more question, I now have a temp page to test the codes but I looked at the table I created and wondered if I could have the user sort the information. Right now, it's sorted by the release date (desc) but I'd like to have links for the user to sort by the movie title or gross.

I did a google search and I did find one thing that pertained to what I want but didn't understand what it meant:

"Use mysql's "order by" phrase in your select statement to determine ordering; this makes the code you use to convert from data -> html presentation pretty much independent of any ordering. Use a query string in the HTML you place in the column headers that calls the same php file; all you do is use that query string when you need to construct your select statement."

I get the first part of it about ordering but what about using a query string in the html?

**TweezerMan** · July 30, 2005

A "query string in the HTML" is the "?" and what follows it in a link:

>http://www.my-TCH-domain.com/folder/deleteactor.php?id=3

In the above URL, "?id=3" is a query string.

What the page was telling you is that you need to construct the column heading links with a query string to indicate which column to sort by. For example, you could append "?order=title" or "?order=gross" to the links, or use numbers instead of column names ("?order=1", "?order=2", etc.).

Your script would then need to read the 'order' parameter sent in the link, and based on its value, use the appropiate "ORDER BY" clause in your SQL query to order the records the way you want.

Sign In

Mysql Warning Question

Recommended Posts

TheMovieman

TweezerMan

TheMovieman

TweezerMan

TheMovieman

TweezerMan

Join the conversation

Hosting