error in your SQL syntax; check the manual that corresponds to your MariaDB server

eastgod · #1 (**permalink**) January 4th, 2019, 09:49 AM

Hi,
I am learning with one of your book [WROX - begining php6, apache, mysql webdevlopment] and I encounter an error on building a cms. I can not post into the database using the cms_compose.php and the cms_review_article can not fetch any data from database.

see cms_review_article.php

<?php
require 'db.inc.php';
require 'cms_output_functions.inc.php';
include 'cms_header.inc.php';
$db = mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD) or
die ('Unable to connect. Check your connection parameters.');

mysql_select_db(MYSQL_DB, $db) or die(mysql_error($db));

$article_id = (isset($_GET['article_id']) && ctype_digit($_GET['article_id'])) ? $_GET['article_id'] :'';

echo '<h2> Article Review </h2> ';
output_story($db, $article_id);

$sql = 'SELECT is_published, UNIX_TIMESTAMP(publish_date) AS publish_date, access_level
FROM
cms_articles a INNER JOIN cms_users u ON a.user_id = u.user_id
WHERE
article_id =' . $article_id;

$result = mysql_query($sql, $db) or die(mysql_error());

$row = mysql_fetch_array($result);
extract($row);

mysql_free_result($result);
if (!empty($date_published) and $is_published) {
echo ' <h4> Published: ' . date('l F j, Y H:i', $date_published) . ' </h4> ';
}
?>
<form method="post" action="cms_transact_article.php">
<div>
<input type="submit" name="action" value="edit"/>
<?php
if ($access_level > 1 || $_SESSION['access_level'] > 1) {
if ($is_published) {

echo '<input type="submit" name="action" value="Retract"/>';
} else {
echo '<input type="submit" name="action" value="Publish"/>';
echo '<input type="submit" name="action" value="Delete"/>';
}
}
?>
<input type="hidden" name="article_id" value=" <?php echo $article_id;?> "/>
</div>
</form>
<?php
include 'cms_footer.inc.php';
?>

(see my cms_pending.php)

<?php
require 'db.inc.php';
include 'cms_header.inc.php';
$db = mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD) or
die ('Unable to connect. Check your connection parameters.');

mysql_select_db(MYSQL_DB, $db) or die(mysql_error($db));

echo ' <h2> Article Availability </h2> ';
echo ' <h3> Pending Articles </h3> ';
$sql = 'SELECT
article_id, title, UNIX_TIMESTAMP(submit_date) AS submit_date
FROM
cms_articles
WHERE
is_published = FALSE
ORDER BY
title ASC';
$result = mysql_query($sql, $db) or die(mysql_error($db));
if (mysql_num_rows($result) == 0) {
echo '<p><strong>No pending articles available. </strong> </p> ';
} else {
echo '<ul>';
while ($row = mysql_fetch_array($result)) {
echo ' <li> <a href="cms_review_article.php?article_id=' .
$row['article_id'] . '"> ' . htmlspecialchars($row['title']) .
'</a> (' . date('F j, Y', $row['submit_date']) . ') </li> ';
}
echo '</ul> ';
}
mysql_free_result($result);
echo ' <h3> Published Articles </h3> ';
$sql = 'SELECT
article_id, title, UNIX_TIMESTAMP(publish_date) AS publish_date
FROM
cms_articles
WHERE
is_published = TRUE
ORDER BY
title ASC';
$result = mysql_query($sql, $db) or die(mysql_error($db));

if (mysql_num_rows($result) == 0) {
echo '<p> <strong> No published articles available. </strong> </p> ';
} else {
echo ' <ul> ';
while ($row = mysql_fetch_array($result)) {
echo ' <li> < a href="cms_review_article.php?article_id=' .
$row['article_id'] . '" > ' . htmlspecialchars($row['title']) .
'</a> (' . date('F j, Y', $row['publish_date']) . ') </li> ';
}
echo '</ul> ';
}
mysql_free_result($result);
include 'cms_footer.inc.php';
?>

(see my cms_compose.php)

<?php
require 'db.inc.php';
include 'cms_header.inc.php';
$db = mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD) or
die ('Unable to connect. Check your connection parameters.');

mysql_select_db(MYSQL_DB, $db) or die(mysql_error($db));

$action = (isset($_GET['action'])) ? $_GET['action'] : '';
$article_id = (isset($_GET['article_id']) && ctype_digit($_GET['article_id']))? $_GET['article_id'] : '' ;

$title = (isset($_POST['title'])) ? $_POST['title'] : '';
$article_text = (isset($_POST['article_text'])) ? $_POST['article_text'] : '' ;
$user_id = (isset($_POST['user_id'])) ? $_POST['user_id'] : '' ;

if ($action == 'edit' && !empty($article_id)) {
$sql = 'SELECT
title, article_text, user_id

FROM
cms_articles

WHERE
article_id = ' . $article_id;

$result = mysql_query($sql, $db) or die(mysql_error($db));
$row = mysql_fetch_array($result);

extract($row);

mysql_free_result($result);
}
?>
<h2 > Compose Article </h2>
<form method="post" action="cms_transact_article.php">
<table>
<tr>
<td> <label for="title"> Title: </label> </td>
<td> <input type="text" name="title" id="title" maxlength="255"
value=" <?php echo htmlspecialchars($title); ?> "/> </td>
</tr> <tr>
<td> <label for="article_text"> Text: </label> </td>
<td> <textarea name="article_text" name="article_text" rows="10"
cols="60"> <?php echo htmlspecialchars($article_text); ?> </textarea> </td>
</tr> <tr>
<td> </td>
<td>
<?php
if ($_SESSION['access_level'] < 2) {
echo '<input type="hidden" name="user_id" value="'. $user_id . '"/>';
}
if (empty($article_id)) {
echo ' <input type="submit" name="action" "value="Submit New Article"/> ';
} else {
echo '<input type="hidden" name="article_id" value="' . $article_id . '"/> ';
echo ' <input type="submit" name="action" "value="Save Changes"/> ';
}
?>
</td>
</tr>
</table>
</form>
<?php
require_once 'cms_footer.inc.php';
?>

(see my cms_transact_article.php)

<?php
require_once 'db.inc.php';
require_once 'cms_http_functions.inc.php';
$db = mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD) or
die ('Unable to connect. Check your connection parameters.');

mysql_select_db(MYSQL_DB, $db) or die(mysql_error($db));

if (isset($_REQUEST['action'])) {
switch ($_REQUEST['action']) {
case 'Submit New Article':
$title = (isset($_POST['title'])) ? $_POST['title'] : '';
$article_text = (isset($_POST['article_text'])) ? $_POST['article_text']: '';

if (isset($_SESSION['user_id']) && !empty($title) && !empty($article_text)) {

$sql = 'INSERT INTO cms_articles
(user_id, submit_date, title, article_text)
VALUES
(' . $_SESSION['user_id'] . ',
"' . date('Y-m-d H:i:s') . '",
"' . mysql_real_escape_string($title, $db) . '",
"' . mysql_real_escape_string($article_text, $db) . '")';

mysql_query($sql, $db) or die(mysql_error($db));
}
redirect('cms_index.php');
break;
case 'edit':
redirect('cms_compose.php?action=edit & article_id=' .
$_POST['article_id']);
break;
case 'Save Changes':
$article_id = (isset($_POST['article_id'])) ? $_POST['article_id']
: '';
$user_id = (isset($_POST['user_id'])) ? $_POST['user_id'] : '';
$title = (isset($_POST['title'])) ? $_POST['title'] : '';
$article_text = (isset($_POST['article_text'])) ?
$_POST['article_text']
: '';
if (!empty($article_id) && !empty($title) && !empty($article_text)) {

$sql = 'UPDATE cms_articles SET
title = "' . mysql_real_escape_string($title, $db) . '",
article_text = "' . mysql_real_escape_string($article_text,$db) . '",

submit_date = "' . date('Y-m-d H:i:s') . '"
WHERE
article_id = ' . $article_id;
if (!empty($user_id)) {
$sql .= ' AND user_id = ' . $user_id;
}
mysql_query($sql, $db) or die(mysql_error($db));
}
if (empty($user_id)) {
redirect('cms_pending.php');
} else {
redirect('cms_cpanel.php');
}
break;
case 'Publish':
$article_id = (isset($_POST['article_id'])) ? $_POST['article_id']
: '';
if (!empty($article_id)) {
$sql = 'UPDATE cms_articles SET
is_published = TRUE,
publish_date = "' . date('Y-m-d H:i:s') . '"
WHERE
article_id = ' . $article_id;
mysql_query($sql, $db) or die(mysql_error($db));
}
redirect('cms_pending.php');
break;
case 'Retract':
$article_id = (isset($_POST['article_id'])) ? $_POST['article_id']
: '';
if (!empty($article_id)) {
$sql = 'UPDATE cms_articles SET
is_published = FALSE,
publish_date = â0000-00-00 00:00:00â
WHERE
article_id = ' . $article_id;
mysql_query($sql, $db) or die(mysql_error($db));
}
redirect('cms_pending.php');
break;
case 'Delete':
$article_id = (isset($_POST['article_id'])) ? $_POST['article_id']
: '';
if (!empty($article_id)) {
$sql = 'DELETE a, c FROM
cms_articles a LEFT JOIN cms_comments c ON
a.article_id = c.article_id
WHERE
a.article_id = ' . $article_id . ' AND
is_published = FALSE';
mysql_query($sql, $db) or die(mysql_error($db));
}
redirect('cms_pending.php');
break;
case 'Submit Comment':
$article_id = (isset($_POST['article_id'])) ? $_POST['article_id']
: '';
$comment_text = (isset($_POST['comment_text'])) ?
$_POST['comment_text'] : '';
if (isset($_SESSION['user_id']) && !empty($article_id) &&
!empty($comment_text)) {
$sql = 'INSERT INTO cms_comments
(article_id, user_id, comment_date, comment_text)
VALUES
(' . $article_id . ',
' . $_SESSION['user_id'] . ',
"' . date('Y-m-d H:i:s') . '",
"' . mysql_real_escape_string($comment_text, $db)
. '")';
mysql_query($sql, $db) or die(mysql_error($db));
}
redirect('cms_view_article.php?article_id=' . $article_id);
break;
default:
redirect('cms_index.php');
}
} else {
redirect('cms_index.php');
}
?>

but i kept having:

on cms_review_article.php

error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 5

Please assist. what should I do? Can someone help re-write the troubled code?

jminatel · #2 (**permalink**) January 4th, 2019, 10:33 AM

I suspect that one of the problems is that the Wrox PHP6 books books were prematurely accidentally published and then immediately discontinued because there never was an official PHP6 final release. It stayed in alpha or beta then was discontinued. PHP 5.3 was actually released AFTER PHP6 was discontinued then then PHP numbering went on up through later 5.x versions and on to PHP 7. It's possible that this code can be made to work but I also doubt anyone has tried in 8 years, it's been that long since PHP 6 was abandoned. Sorry if you got an OLD book somewhere.