FAQ  |  Search  |  Register  |  Profile  |  Log in to check your private messages  |  Log in
Problem with SQL queries

 
Post new topic   Reply to topic    Aprelium Forum Index -> PHP
View previous topic :: View next topic  
Author Message
p3
-


Joined: 17 Jun 2005
Posts: 615
PostPosted: Sun Nov 13, 2005 4:13 pm    Post subject: Problem with SQL queries Reply with quote
Hi all! I'm writing a PHP script.... well, just because I can :D

I'm having problems with the SQL syntax in this script. The line it gave me is no where near SQL syntax, so I really have no clue. (Yes, I know, it counts the lines of SQL syntax... the line number is bigger than the number of lines of SQL syntax.)

Here's the script, any ideas as to what the problem could be?

Code:

<?php
//Include config
include('config.php');

echo "Creating Tables required by P3~CMS...<Br>";

$createdatabase = "CREATE DATABASE P3_CMS";
//Create Database

$makedatabase = mysql_query($createdatabase)
         or die (mysql_error());
//Select Database
mysql_select_db("P3_CMS")
   or die (mysql_error());
   
//Create "Posts" table

$posts = "CREATE TABLE posts (
   post_name text NOT NULL,
   post_id int(11) NOT NULL auto_increment,
   post_content text NOT NULL,
   post_date date NOT NULL,
   PRIMARY KEY (post_id)
   )";
   
$postcreate = mysql_query($posts)
            or die (mysql_error());
echo "Table 'posts' created sucessfully<br>";

//Create Config Table

$config = "CREATE TABLE config (
   config_sitename text NOT NULL,
   config_siteurl text NOT NULL,
   config_admin_username text NOT NULL,
   config_admin_password text NOT NULL,
   )";
   
$configcreate = mysql_query($config)
               or die (mysql_error());
echo "Table 'config' created sucessfully<br>";
echo "All tables created sucessfully.<br>";
echo "Creating sample post...<br>";

$date = date('Y-m-d');
$samplepost ="INSERT INTO posts (post_name, post_content, post_id, post_date)
('Sample Post', 'This posts confirms that P3~CMS if functioning properly. You may delete it from the <a href='admin'>admin</a> area.', '1', '$date')";

$createsamplepost = mysql_query($samplepost)
                  or die (mysql_error());
echo "Sample post created sucessfully.<br>";
echo "Inserting default values into table 'config...<br>";

$configdefault = "INSERT INTO config (config_sitename, config_url, config_admin_username, config_admin_password)
('Your Site Name', 'http://yourdomain.com', 'admin', 'password')";
echo "Default values set. Default values are:<br>
Sitename -> 'Your Site Name'<br>
Site URL -> 'http://yourdomain.com'<br>
Admin Username -> admin<br>
Admin Password -> password<br>
Please change these values in the admin panel as soon as possible.<br>";
echo "<a href='admin'>Click here to proceed to the admin.</a>";
?>


PS - This is actually part of a larger script I'm writing... a news system for someone I know's website...

Thanks!

<EDIT>
I forgot to say this :D
Using MySQL 4 and PHP 5.0.3
</EDIT>
<EDIT AGAIN>
Made a few changes. The error is this:
Quote:

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 ')' at line 6

I updated the script up there as well.
</EDIT>
Back to top View user's profile Send private message Send e-mail
roganty
-


Joined: 08 Jun 2004
Posts: 357
Location: Bristol, UK
PostPosted: Sun Nov 13, 2005 4:35 pm    Post subject: Re: Problem with SQL queries Reply with quote
i believe that your problem may lie in this line:
Code:
$samplepost ="INSERT INTO posts (post_name, post_content, post_id, post_date)
('Sample Post', 'This posts confirms that P3~CMS if functioning properly. You may delete it from the <a href='admin'>admin</a> area.', '1', '$date')";


This should fix it:
Code:
$samplepost ="INSERT INTO posts (post_name, post_content, post_id, post_date)
('Sample Post', 'This posts confirms that P3~CMS if functioning properly. You may delete it from the <a href=\'admin\'>admin</a> area.', '1', '$date')";

_________________
Anthony R

Roganty | Links-Links.co.uk
Back to top View user's profile Send private message Visit poster's website
MonkeyNation
-


Joined: 05 Feb 2005
Posts: 921
Location: Cardiff
PostPosted: Sun Nov 13, 2005 4:37 pm    Post subject: Reply with quote
Roganty is correct. However, I don't think that's what's causing this error.
After a quick read, I think it could be:
Code:
$config = "CREATE TABLE config (
   config_sitename text NOT NULL,
   config_siteurl text NOT NULL,
   config_admin_username text NOT NULL,
   config_admin_password text NOT NULL,
   )";

There need not be a comma before the closing bracket there, as far as I know.
_________________
Back to top View user's profile Send private message AIM Address Yahoo Messenger MSN Messenger ICQ Number
p3
-


Joined: 17 Jun 2005
Posts: 615
PostPosted: Sun Nov 13, 2005 4:55 pm    Post subject: Reply with quote
OK, I'll try and see.
Back to top View user's profile Send private message Send e-mail
p3
-


Joined: 17 Jun 2005
Posts: 615
PostPosted: Sun Nov 13, 2005 5:04 pm    Post subject: Reply with quote
Yet another error!:

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 ''Sample Post', 'This posts confirms that P3_CMS if functioning properly. You may' at line 2

PS - I took the ~ out of the syntax to make sure it wasn't the problem. That's why it has an underscore now... I also made all the changes you reccomended, but I didn't change the topic opener.
Back to top View user's profile Send private message Send e-mail
MonkeyNation
-


Joined: 05 Feb 2005
Posts: 921
Location: Cardiff
PostPosted: Sun Nov 13, 2005 5:06 pm    Post subject: Reply with quote
Code:
$samplepost ="INSERT INTO posts (post_name, post_content, post_id, post_date) VALUES ('Sample Post', 'This posts confirms that P3~CMS if functioning properly. You may delete it from the <a href=\'admin\'>admin</a> area.', '1', '$date')";

_________________
Back to top View user's profile Send private message AIM Address Yahoo Messenger MSN Messenger ICQ Number
p3
-


Joined: 17 Jun 2005
Posts: 615
PostPosted: Sun Nov 13, 2005 5:49 pm    Post subject: Reply with quote
MonkeyNation wrote:
Code:
$samplepost ="INSERT INTO posts (post_name, post_content, post_id, post_date) VALUES ('Sample Post', 'This posts confirms that P3~CMS if functioning properly. You may delete it from the <a href=\'admin\'>admin</a> area.', '1', '$date')";

Works like a charm!
Back to top View user's profile Send private message Send e-mail
p3
-


Joined: 17 Jun 2005
Posts: 615
PostPosted: Sun Nov 13, 2005 5:52 pm    Post subject: Reply with quote
New problem with another page:

Code:

<?php
echo "<center">
//Include config file
include('config.php');
//Select P3~CMS Database
mysql_select_db("P3_CMS")
   or die(mysql_error());

//Output index page

//Get Site Name

$sitename= "SELECT config_sitename FROM config";

$getsitename= mysql_query($sitename)
               or die (mysql_error());
echo "<center><font size= '5'>";
while ($rows=mysql_fetch_assoc($getsitename)) {
   foreach($rows as $value) {
      echo $value;
   }
}
echo "</center></font><br><br>";
//Creates Tables
echo "<table><tr><td width = '500'>";
//Gets First Five Posts

$fiveposts = "SELECT post_id, post_name, post_content, post_date FROM posts ORDER BY DESC post_id LIMIT 5";

$getfirstposts = mysql_query($fiveposts)
                        or die (mysql_error());
echo "<center>";
while ($rows=mysql_fetch_assoc($getfirstposts) {
   echo "<b><u>$row['post_name']</u></b><Br><br><br>$row['post_content']<br><br><font size='2']Post created
   $row['post_date']";
   }
echo "</center></td>";
//Navigation (Right Table)
echo "<td width='200'>Navigation:<br><br>
<a href='archives.php'>Archives</a><br>
<a href='admin'>Admin Area</a><br>";
//Get Site URL for link back to site
$siteurl= "SELECT config_siteurl FROM config";
$geturl= mysql_query($siteurl)
         or die (mysql_error());
while ($rows=mysql_fetch_assoc($geturl) {
   $site_url = $row['config_siteurl']
}
echo "<a href=$site_url>Return to Site</a>";
echo "</td>";
echo "<font size='1'><a href='p3net.net'>Powered by P3NET~CMS</a>";
?>

Error is:

Quote:

[13-Nov-2005 08:55:24] PHP Parse error: syntax error, unexpected '{' in ...\htdocs\new_s\index.php on line 33

Back to top View user's profile Send private message Send e-mail
MonkeyNation
-


Joined: 05 Feb 2005
Posts: 921
Location: Cardiff
PostPosted: Sun Nov 13, 2005 6:01 pm    Post subject: Reply with quote
Code:
C:\PHP>php -l syntax.php
PHP Parse error:  syntax error, unexpected '{' in syntax.php on line 33

Parse error: syntax error, unexpected '{' in syntax.php on line 33
Errors parsing syntax.php

C:\PHP>php -l syntax.php
PHP Parse error:  syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting
T_STRING or T_VARIABLE or T_NUM_STRING in syntax.php on line 34

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STR
ING or T_VARIABLE or T_NUM_STRING in syntax.php on line 34
Errors parsing syntax.php

C:\PHP>php -l syntax.php
PHP Parse error:  syntax error, unexpected '{' in syntax.php on line 45

Parse error: syntax error, unexpected '{' in syntax.php on line 45
Errors parsing syntax.php

C:\PHP>php -l syntax.php
PHP Parse error:  syntax error, unexpected '}' in syntax.php on line 47

Parse error: syntax error, unexpected '}' in syntax.php on line 47
Errors parsing syntax.php

C:\PHP>php -l syntax.php
No syntax errors detected in syntax.php


Syntax errors repaired:
Code:
<?php
echo "<center>"; // No semicolon, > before ".
//Include config file
include('config.php');
//Select P3~CMS Database
mysql_select_db("P3_CMS")
   or die(mysql_error());

//Output index page

//Get Site Name

$sitename= "SELECT config_sitename FROM config";

$getsitename= mysql_query($sitename)
               or die (mysql_error());
echo "<center><font size= '5'>";
while ($rows=mysql_fetch_assoc($getsitename)) {
   foreach($rows as $value) {
      echo $value;
   }
}
echo "</center></font><br><br>";
//Creates Tables
echo "<table><tr><td width = '500'>";
//Gets First Five Posts

$fiveposts = "SELECT post_id, post_name, post_content, post_date FROM posts ORDER BY DESC post_id LIMIT 5";

$getfirstposts = mysql_query($fiveposts)
                        or die (mysql_error());
echo "<center>";
while ($rows=mysql_fetch_assoc($getfirstposts)) { //Missing a )
  echo "<b><u>".$row['post_name']."</u></b><Br><br><br>".$row['post_content']."<br><br><font size='2'>Post created ".$row['post_date']; // Added in "."s instead to make cleaner
}
echo "</center></td>";
//Navigation (Right Table)
echo "<td width='200'>Navigation:<br><br>
<a href='archives.php'>Archives</a><br>
<a href='admin'>Admin Area</a><br>";
//Get Site URL for link back to site
$siteurl= "SELECT config_siteurl FROM config";
$geturl= mysql_query($siteurl)
         or die (mysql_error());
while ($rows=mysql_fetch_assoc($geturl)) { //Missing a )
   $site_url = $row['config_siteurl']; //Missing a semicolon
}
echo "<a href=$site_url>Return to Site</a>";
echo "</td>";
echo "<font size='1'><a href='p3net.net'>Powered by P3NET~CMS</a>";
?>

_________________
Back to top View user's profile Send private message AIM Address Yahoo Messenger MSN Messenger ICQ Number
p3
-


Joined: 17 Jun 2005
Posts: 615
PostPosted: Sun Nov 13, 2005 6:59 pm    Post subject: Reply with quote
Thank you. Now we have an SQL Error when running:

Quote:

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 'DESC post_id LIMIT 5' at line 1
Back to top View user's profile Send private message Send e-mail
MonkeyNation
-


Joined: 05 Feb 2005
Posts: 921
Location: Cardiff
PostPosted: Sun Nov 13, 2005 7:04 pm    Post subject: Reply with quote
Code:
$fiveposts = "SELECT post_id, post_name, post_content, post_date FROM posts ORDER BY post_id DESC LIMIT 5";

Wrong order.
_________________
Back to top View user's profile Send private message AIM Address Yahoo Messenger MSN Messenger ICQ Number
p3
-


Joined: 17 Jun 2005
Posts: 615
PostPosted: Sun Nov 13, 2005 7:32 pm    Post subject: Reply with quote
MonkeyNation wrote:
Code:
$fiveposts = "SELECT post_id, post_name, post_content, post_date FROM posts ORDER BY post_id DESC LIMIT 5";

Wrong order.

OK, trying...
Back to top View user's profile Send private message Send e-mail
p3
-


Joined: 17 Jun 2005
Posts: 615
PostPosted: Sun Nov 13, 2005 7:35 pm    Post subject: Reply with quote
I think something isn't working... it doesn't display the post or the default site name, etc. It just says:



Powered by P3NET~CMS



Post created

Then on the right it says:

Navigation:

Archives
Admin Area
Return to Site

So it isn't retrieving the post...
Back to top View user's profile Send private message Send e-mail
MonkeyNation
-


Joined: 05 Feb 2005
Posts: 921
Location: Cardiff
PostPosted: Sun Nov 13, 2005 8:26 pm    Post subject: Reply with quote
p3 wrote:
I think something isn't working... it doesn't display the post or the default site name, etc. It just says:



Powered by P3NET~CMS



Post created

Then on the right it says:

Navigation:

Archives
Admin Area
Return to Site

So it isn't retrieving the post...


You used $rows to store the array, and $row to show it.
_________________
Back to top View user's profile Send private message AIM Address Yahoo Messenger MSN Messenger ICQ Number
Display posts from previous:   
Post new topic   Reply to topic    Aprelium Forum Index -> PHP All times are GMT + 1 Hour
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB phpBB Group