Jump to content
Sign in to follow this  
SpeedCrazy

PHP MYSQL Problem sending me around the bend.

Recommended Posts

Okay, before anyone calls me on reinventing the wheel this is from a tutorial.

I am building a basic database CMS,

I have the following tables in my db:

CREATE TABLE cmsPAGE (
cmsPageID INTEGER PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(30)
);
CREATE TABLE cmsBLOCK(
cmsBlockID INTEGER PRIMARY KEY AUTO_INCREMENT,
blockTypeID INTEGER,
title VARCHAR(50),
content TEXT,
pageID INTEGER
);
CREATE TABLE blockType (
blockTypeID INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30)

With data in those tables i created a view:

CREATE VIEW pageView AS
SELECT
blockType.name as 'block',
cmsBlock.title as 'title',
cmsBlock.content as 'content',
cmsBlock.pageID as 'pageID',
cmsPage.title as 'page'
FROM 
cmsBlock, blockType, cmsPage
WHERE
cmsBlock.blockTypeID = blockType.blockTypeID;

This all is read by a php file:

<!doctype html>
<!-- paulirish.com/2008/conditional-stylesheets-vs-css-hacks-answer-neither/ -->
<!--[if lt IE 7]> <html class="no-js ie6" lang="en"> <![endif]-->
<!--[if IE 7]>    	<html class="no-js ie7" lang="en"> <![endif]-->
<!--[if IE 8]>		<html class="no-js ie8" lang="en"> <![endif]-->
<!--[if gt IE 8]><!--> <html class="no-js" lang="en"> <!--<![endif]-->
<head>
 <meta charset="utf-8">
 <title>new_file.html</title>
 <link type="text/css" rel="stylesheet" media="screen" href="css/style.css">

</head>
<?php
//get page id from request if possible
$pageID=1;

//read current page info from db
$conn = mysql_connect("localhost", "cms","");
mysql_select_db("cms");
$pageID = mysql_real_escape_string($pageID, $conn);
$sql = "SELECT * FROM pageview WHERE pageID = $pageID";
$result = mysql_query($sql, $conn);

// populate local variables from db query result
if ($result){
while ($row = mysql_fetch_assoc($result)){
	if ($row["block"] == "head"){
		$head = $row["title"];
	} else if ($row["block"] == "menu"){
		$menu = $row["content"];
	} else if($row["block"] == "content1"){
		$c1Title = $row["title"];
		$c1Text = $row["content"];
	} else if($row["block"] == "content2"){
		$c2Title = $row["title"];
		$c2Text = $row["content"];
	} else if($row["block"] == "footer"){
		$footer = $row["content"];
	}// end if
}// end while
} else (print "Error")
?>
<body>

<div id="all">

	<div id="heading">
		<h1>
			<?php print $head ?>
		</h1>
	</div><!-- end #heading -->

	<div id="menu">
		<?php print $menu ?>
	</div><!-- end #menu -->

	<div class="content">
		<h2>
			<?php print $c1title; ?>
		</h2>
		<p>
			<?php print $c1Text; ?>
		</p>
	</div><!-- end content div -->

	<div class="content">
		<h2>
			<?php print $c2title; ?>
		</h2>
		<p>
			<?php print $c2Text; ?>
		</p>
	</div><!-- end content div -->

	<div id="footer">
		<?php print $footer ?>
	</div><!-- end #footer -->

</div><!-- end #all -->

</body>
</html>

The problem is that it doesn't seem to connect to the db, all i get is error. I also tried printing $result but it appears to be an empty value. The database is called "cms", there is no password, i removed it to eliminate another variable, same reason that "$pageID= 1" instead of receiving that from user input.

I am hoping it is a silly mistake, but whatever it is, has anyone got any ideas?

Thanks,

Speed

Share this post


Link to post
Share on other sites

Just took a quick glance but I think it could be working correctly. mysql_query returns false on error and a resource one success (when it's a select query as in your example). See this page and look at the return values. I would change your if statement to

if($result !== false){
  // success code here
}
else{
 // error code here
}

This checks to see if $result is actually the boolean false, or not.

 

Also, I wouldn't put my print statement outside of my body tags. If it fails you want it to fail gracefully.

I would also use a class for my mysql. Makes it easier to change things in the future.

Share this post


Link to post
Share on other sites

Okay, that lines up with the error code i was receiving before i put in the if else error clause. Something to do with wanting a resource instead of a Boolean. So really that just tells me that its failing not why its failing.

 

Btw, what do you mean about using a class for mysql?

As for printing in the body, i know, it was a temporary measure.

Share this post


Link to post
Share on other sites

$conn = mysql_connect("localhost", "cms","");

server= localhost, user = cms password is blank. Thought that gave me an idea, could having both the user and the db name as cms confuse it?

 

EDIT: I went back and reduced the server query to just "SELECT title FROM cmsPage" and then printed that. Still no dice, so my connection is screwed up somewhere.

 

EDIT2: Must be that, i got rid of the pageview and used the following select statement. Still doesn't work.

SELECT
blocktype.name as 'block',
cmsblock.title as 'title',
cmsblock.content as 'content',
cmsblock.pageID as 'pageID',
cmspage.title as 'page'
FROM 
cmsblock, blocktype, cmspage
WHERE
cmsblock.blockTypeID = blocktype.blockTypeID;

Edited by SpeedCrazy

Share this post


Link to post
Share on other sites

Mysql is running right? lol... sorry gotta ask. Are you able to connect to the database in any other way?

Share this post


Link to post
Share on other sites

yes mysql is running, i am using myphpadmin.

Share this post


Link to post
Share on other sites

Okay, i have experimented and tried every other php/mysql program i have and none of the mcurrently work. There seems to be a problem with connecting to the database. I can go line by line with an

or die(print"Error".mysql_error())

and it will throw it every where there is a bd query, unfortunately it wont show the error code, just my Error statement.

Any ideas on what could be wrong? I'm clueless. :dunno:

Share this post


Link to post
Share on other sites

Breakthrough!!! I got it to throw an error.

No database selected.

How is that possible.

Here is the current code.

<?php
//get page id from request if possible
$pageID=1;

//read current page info from db
$conn = mysql_connect("localhost", "dbcms");
mysql_select_db("cms", $conn);
$pageID = mysql_real_escape_string($pageID, $conn);
$sql = "SELECT
blocktype.name as 'block',
cmsblock.title as 'title',
cmsblock.content as 'content',
cmsblock.pageID as 'pageID',
cmspage.title as 'page'
FROM 
cmsblock, blocktype, cmspage
WHERE
cmsblock.blockTypeID = blocktype.blockTypeID;";
$result = mysql_query($sql, $conn);
if (!$result) {
   $message  = 'Invalid query: ' . mysql_error() . "\n";
   die($message);
}
// populate local variables from db query result
if ($result){
while ($row = mysql_fetch_assoc($result)){
	if ($row["block"] == "head"){
		$head = $row["title"];
	} else if ($row["block"] == "menu"){
		$menu = $row["content"];
	} else if($row["block"] == "content1"){
		$c1Title = $row["title"];
		$c1Text = $row["content"];
	} else if($row["block"] == "content2"){
		$c2Title = $row["title"];
		$c2Text = $row["content"];
	} else if($row["block"] == "footer"){
		$footer = $row["content"];
	}// end if
}// end while
} else (print "Error")
?>

What now???

 

Stepped back to just try the connection with this:

if (!$db_selected) {
   die ( mysql_error());
}

after putting the db select in a variable $db_selected, and i get:

Acces denied for user @localhost using password YES

Turns out i had had caps lock when putting the username into myphpadmin and i hadn't noticed the fact that it was lowercase in my code.

:doh: :doh: :doh: :doh: :doh: :doh: :doh: :doh: :doh: :doh: :doh:

What a n00b. LOL well i sure learned how to pick my code apart and look for errors. I actually managed to refine it a good ways from my original by all this nitpicking.

Thanks guys,

Speed

Edited by SpeedCrazy

Share this post


Link to post
Share on other sites

What I mean by using a class for your mysql is so that you call a function to perform your queries. It makes it easier if you ever need to change something about the way you call your queries (like if you change database types). So you would have a function like

function insert($aVars, $sTable, $aExclude = ''){
   // do query stuff here
}

Then I just call my insert function with the variables included. If you're just learning I wouldn't worry about it. But if you're making something that will be worked on by other people then I'd recommend it.

 

I based mine off of this one

Share this post


Link to post
Share on other sites

Okay, i am still just beginning to scrape the surface of web design and engineering.

So do i have this right, a class is kinda like external css or js, only its php, you can call arguments in the external file from any page linked to it?

Share this post


Link to post
Share on other sites

a class is not just an external file although it will exist in an external file. A class is an object that has variables and its own functions. If you're new to php then I wouldn't worry about classes right. They are useful, but not so useful that I use them very often.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...