mysql_query() can't find an existing table

[juliejohnson26]

First, I'm using MAMP with MySQL client version: 5.5.9.

 

I am very new to php/mysql and have been following along with Kevin Skoglund's tutorial on lynda.com which is very good.  I have successfully created a small database in mysql with three tables, one of which is called pages. In my php file, I have the following loop which is directly from the tutorial:

 

while ($subject = mysql_fetch_array($subject_set)) {

echo "<li>{$subject["menu_name"]}</li>";

$page_set = mysql_query("SELECT * FROM pages WHERE subject_id = {$subject["id"]}", $connection);

if (!$page_set) {

die("Database query failed here: " . mysql_error());

}

 

I am consistently breaking out of the loop after the first echo of the first $subject, with the mysql_error "table 'widget_corp.pages' doesn't exist" (widget_corp being the name of the database).

 

I've attached a screenshot of the pages table structure and browse views in mysql. I'd appreciate any advice on what's going wrong.

 

 

 

[attachment deleted by admin]

[sunfighter]

Are you signing into the database?

 

// Connect to mysql here or die
$link = mysql_connect($DBhost,$username,$password) or die("Unable to connect to host");

// Connect to the database you want here or die
$link = mysql_select_db($database) or die( "Unable to connect database");

[juliejohnson26]

Yes, and I know it's working because  a previous call to  the database doesn't generate any errors:

 

	$subject_set = mysql_query("SELECT * FROM subjects", $connection);
			if (!$subject_set) {
				die("Database query failed: " . mysql_error());
			}

 

Are you signing into the database?

[gizmola]

I don't see anything that is obviously wrong.

 

First thing I would try is restarting mysql and apache. 

 

If that does not solve your problem, I would question the user you are using in the script and its permissions.  How were permissions granted and can it see the pages table.

[juliejohnson26]

I only have the root user which is a superuser. When I log into the database as root through phpmyadmin, I see the pages table just fine (image attached above).

 

If that does not solve your problem, I would question the user you are using in the script and its permissions.  How were permissions granted and can it see the pages table.

[juliejohnson26]

Did this right above the while loop in question and the database is definitely connected.

 

 

// Connect to mysql here or die
$link = mysql_connect($DBhost,$username,$password) or die("Unable to connect to host");

[/quote]

[gizmola]

Did you restart mysql and apache?

 

Please provide the full current source of the script(s) you have right now.

[juliejohnson26]

Sorry, yes. I restarted mysql and apache a few times and still no go.

 

I've attached the scripts.

 

Did you restart mysql and apache?

 

Please provide the full current source of the script(s) you have right now.

 

[attachment deleted by admin]

[gizmola]

I'm really grasping at straws here, as I don't see anything wrong with any of the code you have, but just to rule it out try this in your content.php

 

</pre>
<table id="structure">




			$subject_set = mysql_query("SELECT * FROM subjects", $connection);
			if (!$subject_set) {
				die("Database query failed: " . mysql_error());
			}


while ($subject = mysql_fetch_array($subject_set)) {
	// Connect to mysql here or die
	echo "{$subject["menu_name"]}";
	$page_set = mysql_query("SELECT * FROM `pages` WHERE subject_id = {$subject["id"]}", $connection);
		if (!$page_set) {
			die("Database query failed here: " . mysql_error());
		}

	echo "";
	while ($page = mysql_fetch_array($page_set)) {
		echo "{$page["menu_name"]}";
	}
	echo "";
}


			?>




Content Area



</table>
<br>?&g

 

 

[mikosiko]

your code is wrong... (content.php)

 

	while ($subject = mysql_fetch_array($subject_set)) {
	// Connect to mysql here or die
	$link = mysql_connect("localhost","root","OtlPHP07") or die("Unable to connect to host");
	echo "$link";
	echo "<li>{$subject["menu_name"]}</li>";
	$page_set = mysql_query("SELECT * FROM pages WHERE subject_id = {$subject["id"]}", $connection);
		if (!$page_set) {
			die("Database query failed here: " . mysql_error());

 

you are creating a new connection inside of the loop (OUTCH!!!) and trying to execute a query over a connection ($connection) that not longer exist, hence the error... assuming that the posted code is the one that was failing when you started the thread

[gizmola]

mikosiko,

  That's not the problem.  That code was put in after people suggested that as debugging, but it is neither here nor there, as there is no problem making multiple mysql connections.  In the case of that code, it makes a 2nd mysql connection assigning the resource variable to $link, but she never uses it for anything.  $connection is setup earlier, and a single connection can be used for many mysql queries.  None of this should be a problem.

[juliejohnson26]

Oops...sent a test file that I created while I've been grasping at straws. The following two lines were NOT included in the code that started this thread.

 

 

	// Connect to mysql here or die
	$link = mysql_connect("localhost","root","OtlPHP07") or die("Unable to connect to host");
	echo "$link";

[gizmola]

It's ok, leave that out as it has no bearing on this problem.  Try the small tweak I suggested.

[mikosiko]

mikosiko,

  That's not the problem.  That code was put in after people suggested that as debugging, but it is neither here nor there, as there is no problem making multiple mysql connections.  In the case of that code, it makes a 2nd mysql connection assigning the resource variable to $link, but she never uses it for anything.  $connection is setup earlier, and a single connection can be used for many mysql queries.  None of this should be a problem.

 

That is why I put the comment "assuming... etct..etc"... and "as there is no problem making multiple mysql connections"... is true only if you are not reaching the Max_connection limit defined for your installation

[PFMaBiSmAd]

I'm going to guess that your actual table name has some non-printing character as part of the name. Perhaps rename the table to something else, then rename it back to pages.

[gizmola]

mikosiko,

  That's not the problem.  That code was put in after people suggested that as debugging, but it is neither here nor there, as there is no problem making multiple mysql connections.  In the case of that code, it makes a 2nd mysql connection assigning the resource variable to $link, but she never uses it for anything.  $connection is setup earlier, and a single connection can be used for many mysql queries.  None of this should be a problem.

 

That is why I put the comment "assuming... etct..etc"... and "as there is no problem making multiple mysql connections"... is true only if you are not reaching the Max_connection limit defined for your installation

 

Again, this was not the problem, as she described in her first message that she received an error saying it couldn't find the table.

[gizmola]

I'm going to guess that your actual table name has some non-printing character as part of the name. Perhaps rename the table to something else, then rename it back to pages.

 

Good hypothesis. 

[mikosiko]

gizmola...you are not reading/analyzing my answers are you?  .... but is ok... doesn't matter.... PBAb could be right.

[gizmola]

gizmola...you are not reading/analyzing my answers are you?  .... but is ok... doesn't matter.... PBAb could be right.

 

Mikosiko,

  I did read your answers.  You simply didn't read the original question carefully enough and suggested fixes for problems that were irrelevant to the issue.  I have done the same countless times.  With as many experienced developers as we have at phpf, you will inevitably come up with the wrong answer from time to time.  I hope you won't take it personally because your post introduced red herrings that were not helpful, and I stated as much.  The goal here is to help the OP, and it doesn't help them to send them on a wild goose chase.

[juliejohnson26]

Tried this...still nothing...sigh...

 

I'm really grasping at straws here, as I don't see anything wrong with any of the code you have, but just to rule it out try this in your content.php

 

<?php require_once("includes/connection.php"); ?>
<?php require_once("includes/functions.php"); ?>
<?php include("includes/header.php"); ?>
	<table id="structure">
	<tr>
		<td id="navigation">
			<ul class="subjects">
			<?php

			$subject_set = mysql_query("SELECT * FROM subjects", $connection);
			if (!$subject_set) {
				die("Database query failed: " . mysql_error());
			}


while ($subject = mysql_fetch_array($subject_set)) {
	// Connect to mysql here or die
	echo "<li>{$subject["menu_name"]}</li>";
	$page_set = mysql_query("SELECT * FROM `pages` WHERE subject_id = {$subject["id"]}", $connection);
		if (!$page_set) {
			die("Database query failed here: " . mysql_error());
		}

	echo "<ul class=\"pages\">";
	while ($page = mysql_fetch_array($page_set)) {
		echo "<li>{$page["menu_name"]}</li>";
	}
	echo "</ul>";
}


			?>

		</td>
		</ul>
		<td id="page">
			<h2>Content Area</h2>

		</td>
	</tr>
</table>
<?php require("includes/footer.php");
?>

[Muddy_Funster]

I don't know that it will make any difference, but you could try a cut down test page:

$user= "root";
$pass= "OtlPHP07";
$host= "localhost";
$db= "widget_corp";

$con = mysql_connect($host, $user, $pass) or die ("Can't Connect : ".mysql_error());
$cur_db = mysql_select_db($db, $con) or die ("Unable to open Database '$db' : ".mysql_error());

$qry = "SELECT menu_name, content FROM pages ORDER BY id DESC";
$return = mysql_query($qry) or die (Data retetreval failed running : $qry <br><br> with the following error :<br>".mysql_error());
WHILE ($row = mysql_fetch_assoc($return)){
echo "<p>{$row['menu_name']} ---- {$row['content']} </p>"
}

 

Cutting it down to the bare minimum might help find the problem - or not