|
PHP - Mysql Update Not Working Error #1064 - You Have An Error In Your Sql Syntax;
I have been pulling my hair out for the lasy 3 hours i am trying to update a MySql table but i cant get it too work, i just keep getting MySql error #1064 - You have an error in your SQL syntax; if i just update 1 field it works fine but if i try to update more than 1 field it dosent work, Help Please!
<?php $root = $_SERVER['DOCUMENT_ROOT']; require("$root/include/mysqldb.php"); require("$root/include/incpost.php"); $con = mysql_connect("$dbhost","$dbuser","$dbpass"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("$dbame", $con); mysql_query("UPDATE Reg_Profile_p SET build='$build' col='$col' size='$size' WHERE uin = '$uinco'"); ?> Similar TutorialsHello all,
Appreciate if you folks could pls. help me understand (and more importantly resolve) this very weird error:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 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 'ASC, purchase_later_flag ASC, shopper1_buy_flag AS' at line 3' in /var/www/index.php:67 Stack trace: #0 /var/www/index.php(67): PDO->query('SELECT shoplist...') #1 {main} thrown in /var/www/index.php on line 67
Everything seems to work fine when/if I use the following SQL query (which can also be seen commented out in my code towards the end of this post) :
$sql = "SELECT shoplist.*, store_master.store_name, item_master.item_name FROM shoplist, store_master, item_master WHERE shoplist.store_id = store_master.store_id AND shoplist.item_id = item_master.item_id";However, the moment I change my query to the following, which essentially just includes/adds the ORDER BY clause, I receive the error quoted above: $sql = "SELECT shoplist.*, store_master.store_name, item_master.item_name FROM shoplist, store_master, item_master ORDER BY purchased_flag ASC, purchase_later_flag ASC, shopper1_buy_flag ASC, shopper2_buy_flag ASC, store_name ASC) WHERE shoplist.store_id = store_master.store_id AND shoplist.item_id = item_master.item_id";In googling for this error I came across posts that suggested using "ORDER BY FIND_IN_SET()" and "ORDER BY FIELD()"...both of which I tried with no success. Here's the portion of my code which seems to have a problem, and line # 67 is the 3rd from bottom (third last) statement in the code below: <?php
/*
$sql = "SELECT shoplist.*, store_master.store_name, item_master.item_name
FROM shoplist, store_master, item_master
WHERE shoplist.store_id = store_master.store_id
AND shoplist.item_id = item_master.item_id";
*/
$sql = "SELECT shoplist.*, store_master.store_name, item_master.item_name
FROM shoplist, store_master, item_master
ORDER BY FIND_IN_SET(purchased_flag ASC,
purchase_later_flag ASC,
shopper1_buy_flag ASC,
shopper2_buy_flag ASC,
store_name ASC)
WHERE shoplist.store_id = store_master.store_id
AND shoplist.item_id = item_master.item_id";
$result = $pdo->query($sql);
// foreach ($pdo->query($sql) as $row) {
foreach ($result as $row) {
echo '<tr>';
print '<td><span class="filler-checkbox"><input type="checkbox" name="IDnumber[]" value="' . $row["idnumber"] . '" /></span></td>';
Thanks
I have a pdo prepared statement that fetches records from mysql database. The records show up on the page. However if there are no records on a page, I get this error message.
"QLSTATE[42000]: Syntax error or access violation: 1064 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 '-10' at line 4"
Here is my statement
$getCategoryId = $_GET['id'];
$limit = 10;
$offset = ($page - 1) * $limit;
$statement = $db->prepare("SELECT records.*, categories.* FROM records
LEFT JOIN categories ON records.category_id = categories.category_id
WHERE records.category_id = :category_id
ORDER BY record_id DESC LIMIT {$limit} OFFSET ".$offset);
$statement->bindParam('category_id', $getCategoryId);
$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_ASSOC);
If I remove try and catch block, it'll tell me exactly which line is giving the issue. So from the above code, the error has to do with "$statement->execute();". This is where the error occurs.
As far as I know, the above pdo statement is correct. Can you tell me if something is wrong with it?
Edited by helloworld001, 16 December 2014 - 04:14 PM. I'm getting the err msg 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 '$sqry' at line 1 " while executing query from php5.3.5 but its working directly in mysql5.5.8 Help? I’ve just read several manual pages and forum comments on "Error number 1064" but I still don’t understand what’s going on with the problem I'm having now … Also, I'm surprised that the error message mentions two different line numbers (1 and 35). Here is the error message I get : And here are the contents of my "one_more_visit_to_topic.php" file :
<?php
include("includes/model/database_searches/see_readings.php");
include("includes/model/database_searches/see_watchings.php");
function one_more_visit_to_topic($user_id,$topic_id)
{
global $db;
$request_string='UPDATE forum_topic '.
'SET topic_vu = topic_vu + 1 WHERE topic_id = :topic';
$query=$db->prepare($request_string);
$query->bindValue(':topic',$topic_id,PDO::PARAM_INT);
$query->execute();
$query->CloseCursor();
// Remember that topic has been read by user
if(!(see_if_user_has_read_topic($user_id,$topic_id))) {
$request_string='INSERT INTO forum_read (fr_topic_id,fr_user_id) '.
'VALUES (:topic,:user)';
$query=$db->prepare($request_string);
$query->bindValue(':topic',$topic_id,PDO::PARAM_INT);
$query->bindValue(':user',$user_id,PDO::PARAM_INT);
$query->execute();
$query->CloseCursor();
}
// If user has already received a warning by mail,
// the "alert" value needs to be toggled in the fw_watch table
if(see_if_user_has_been_warned($user_id,$topic_id)) {
$request_string='UPDATE forum_read SET fw_alert = :alt '.
'WHERE fw_topic_id = :topic AND fw_user_id= :user)';
$query=$db->prepare($request_string);
$query->bindValue(':topic',$topic_id,PDO::PARAM_INT);
$query->bindValue(':user',$user_id,PDO::PARAM_INT);
$one=(int)1;
$query->bindValue(':alt',$one,PDO::PARAM_INT);
$query->execute();
$query->CloseCursor();
}
return;
}
?>
I have been looking at this code most of the morning and do not have a clue what is wrong with the code. I am hoping its not a stupid mistake, can someone please help me out? thank you
<title>Inputing Travel Detials</title>
<header>
<h1 align="center"> Adding Travel Detials </h1>
<body>
<p> <center><img src="cyberwarfareimage1.png" alt="Squadron logo" style="width:200px;height:200px" style="middle"></center>
<table border="1">
<tr>
<td><a href="index.php"> Home Page </a></td>
<td><a href="administratorhomepage.html">Administrator Home Page </a></td>
<td><a href="viewhomepage.html">View Home Page </a></td>
<td><a href="Inputhomepage.html">Input Home Page </a></td>
<td><a href="traveldetials.html">Enter More Travel Detials </a></td>
</table>
</p>
<?php
include "connection.php";
$Applicant_ID = $_POST["Applicant_ID"];
$Method_Of_Travel = $_POST["Method_Of_Travel"];
$Cost = $_POST["Cost"];
$ETA = $_POST["ETA"];
$Main_Gate_Advised = $_POST["Main_Gate_Advised"];
$query = ("UPDATE `int_board_applicant` SET `Method_Of_Travel`=`$Method_Of_Travel', `Cost`=`$Cost', `ETA`='$ETA', `Main_Gate_Advised`='$Main_Gate_Advised' WHERE `Applicant_ID`='$Applicant_ID'");
$result = mysqli_query($dbhandle, $query)
or die(mysqli_error($dbhandle));
if($result){ echo "Success!"; }
else{ echo "Error."; }
// successfully insert data into database, displays message "Successful".
if($query){
echo "Successful";
}
else {
echo "Data not Submitted";
}
//closing the connection
mysqli_close($dbhandle)
?>
Ok this is puzzleing. I am geting "Could not delete data: 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 '1' at line 1". but its is deleting the entry that needs to be removed. The "1" is the entry. Just not sure what is causing the error. I do have another delete php but I have put that on the back burning for the time being.
<?php $con = mysqli_connect("localhost","user","password","part_inventory"); // Check connection if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } else { $result = mysqli_query($con, "SELECT * FROM amp20 "); $amp20ptid = $_POST['amp20ptid']; // escape variables for security $amp20ptid = mysqli_real_escape_string($con, $_POST['amp20ptid']); mysqli_query($con, "DELETE FROM amp20 WHERE amp20ptid = '$amp20ptid'"); if (!mysqli_query($con, $amp20ptid)); { die('Could not delete data: ' . mysqli_error($con)); } echo "Part has been deleted to the database!!!\n"; mysqli_close($con); } ?>
<?php
//COOKIE CHECKER
if (isset($_COOKIE["person"])){
if (filter_var($_COOKIE["person"], FILTER_VALIDATE_INT)){
$user_id = $_COOKIE["person"];
//DATABASE CONNECTION VARIABLES
$myserver ="localhost";
$myname = "username";
$mypassword = "password";
$mydb ="dbname";
/*SQL CONNECTION*/
// Create connection
$conn = new mysqli($myserver, $myname, $mypassword, $mydb);
// Check connection
if ($conn->connect_error) {
echo '<!DOCTYPE HTML>
<HTML>
<HEAD>
<TITLE>test</TITLE>
</HEAD>
<BODY>
<H1>Decline of the Han - Three Kingdoms</H1>';
die("Connection failed: " . $conn->connect_error);
echo '</BODY>
</HTML>';
}
else {
//COUNT USER
$cquery = "SELECT COUNT(*) AS usercheck FROM Players WHERE ID = ?";
$cid = $conn->prepare($cquery);
$cid->bind_param('i', $user_id);
$cid->execute();
$cid->bind_result($usercheck);
$cid->fetch();
if ($usercheck ==1){
if (isset($_POST["profile"])){
if(!filter_var($_POST["profile"], FILTER_SANITIZE_STRING)){
echo '<!DOCTYPE HTML>
<HTML>
<HEAD>
<TITLE>test</TITLE>
</HEAD>
<BODY>';
echo '<P class="error">Unable to filter bio <a href="biography.php">return</a></P>';
echo '</BODY>
</HTML>';
}
else {
$profile = $_POST["profile"];
$sql = "UPDATE Player_Data SET Bio =? WHERE ID=?";
$q = $conn->prepare($sql);
$q->bind_param("si", $profile, $user_id);
$q->execute();
echo '<P>Biography altered <a href="biography.php">return</a></P>';
//close connection
$conn->close();
}
}
}
else {
echo '<!DOCTYPE HTML>
<HTML>
<HEAD>
<TITLE>test</TITLE>
</HEAD>
<BODY>';
echo '<P class="error">'.$usercheck.' '.$user_id.'</P>';
echo '<P class="error">No such user found!</P>';
//close connection
$conn->close();
//foot
echo '</BODY>
</HTML>';
}
//end connection check
}
}
else {
echo '<!DOCTYPE HTML>
<HTML>
<HEAD>
<TITLE>test</TITLE>
</HEAD>
<BODY>';
echo '<P class="error">ERROR invalid cookie!</P>';
echo '</BODY>
</HTML>';
}
}
else {
echo '<!DOCTYPE HTML>
<HTML>
<HEAD>
<TITLE>test</TITLE>
</HEAD>
<BODY>';
echo '<P class="error">No cookie detected!<br><a href="login.php">login</a></P>';
echo '</BODY>
</HTML>';
}
?>
I have an error in the update, but I am not seeing where I made it. Its annoying because the update won't execute and anything beyond the update isn't visible in the html source code in the browser, so it is likely to be a syntax error, but where?
<?php
//COOKIE CHECKER
if (isset($_COOKIE["person"])){
if (filter_var($_COOKIE["person"], FILTER_VALIDATE_INT)){
$user_id = $_COOKIE["person"];
//DATABASE CONNECTION VARIABLES
$myserver ="localhost";
$myname = "username";
$mypassword = "password";
$mydb ="dbname";
/*SQL CONNECTION*/
// Create connection
$conn = new mysqli($myserver, $myname, $mypassword, $mydb);
// Check connection
if ($conn->connect_error) {
echo '<!DOCTYPE HTML>
<HTML>
<HEAD>
<TITLE>test</TITLE>
</HEAD>
<BODY>
<H1>Decline of the Han - Three Kingdoms</H1>';
die("Connection failed: " . $conn->connect_error);
echo '</BODY>
</HTML>';
}
else {
//COUNT USER
$cquery = "SELECT COUNT(*) AS usercheck FROM Players WHERE ID = ?";
$cid = $conn->prepare($cquery);
$cid->bind_param('i', $user_id);
$cid->execute();
$cid->bind_result($usercheck);
$cid->fetch();
if ($usercheck ==1){
if (isset($_POST["profile"])){
if(!filter_var($_POST["profile"], FILTER_SANITIZE_STRING)){
echo '<!DOCTYPE HTML>
<HTML>
<HEAD>
<TITLE>test</TITLE>
</HEAD>
<BODY>';
echo '<P class="error">Unable to filter bio <a href="biography.php">return</a></P>';
echo '</BODY>
</HTML>';
}
else {
$profile = $_POST["profile"];
$sql = "UPDATE Player_Data SET Bio =? WHERE ID=?";
$q = $conn->prepare($sql);
$q->bind_param("si", $profile, $user_id);
$q->execute();
echo '<P>Biography altered <a href="biography.php">return</a></P>';
//close connection
$conn->close();
}
}
}
else {
echo '<!DOCTYPE HTML>
<HTML>
<HEAD>
<TITLE>test</TITLE>
</HEAD>
<BODY>';
echo '<P class="error">'.$usercheck.' '.$user_id.'</P>';
echo '<P class="error">No such user found!</P>';
//close connection
$conn->close();
//foot
echo '</BODY>
</HTML>';
}
//end connection check
}
}
else {
echo '<!DOCTYPE HTML>
<HTML>
<HEAD>
<TITLE>test</TITLE>
</HEAD>
<BODY>';
echo '<P class="error">ERROR invalid cookie!</P>';
echo '</BODY>
</HTML>';
}
}
else {
echo '<!DOCTYPE HTML>
<HTML>
<HEAD>
<TITLE>test</TITLE>
</HEAD>
<BODY>';
echo '<P class="error">No cookie detected!<br><a href="login.php">login</a></P>';
echo '</BODY>
</HTML>';
}
?>
I have an error in the update, but I am not seeing where I made it. Its annoying because the update won't execute and anything beyond the update isn't visible in the html source code in the browser, so it is likely to be a syntax error, but where?
Hi guys
I have this code below and all works fine when submitting this online application apart from when someone types either ' # & into one of the comment fields in which it throws up the error. Have tried various fixes from across the internet but no joy. Can anyone offer suggestions?
<?php
$con = mysql_connect("localhost:3306","root","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db('sfapp', $con);
$sql="INSERT INTO 'sfapp' ('surname_add','forename_add','dob_add','hometele_add','mobiletele_add','homeadd_add','siblings_add','schoolname_add','headname_add','schooladd_add','schooltele_add','schoolem_add','alevel_add','personstate_add','nameprovided_add','pe_add','se_add','PredGrade_Art','PredGrade_AScience','PredGrade_BusStudies','PredGrade_Electronics','PredGrade_EnglishLang','PredGrade_EnglishLit','PredGrade_French','PredGrade_German','PredGrade_Geog','PredGrade_Graphics','PredGrade_History','PredGrade_Maths','PredGrade_SepScience','PredGrade_ProductDesign','PredGrade_Spanish','PredGrade_Other','Gender_Male','Gender_Female','Sub_EnglishLit','Sub_Maths','Sub_FurtherMaths','Sub_Biology','Sub_Chemistry','Sub_Physics','Sub_French','Sub_German','Sub_Spanish','Sub_Geography','Sub_History','Sub_RE','Sub_FineArt','Sub_Business','Sub_Computing','Sub_GlobPersp','Sub_DramaAndTheatre','Sub_PE','Sub_Dance','Sub_Politics','Sub_Psychology','Sub_Sociology','readprospect_chk','Sib_Yes','Sib_No','Current_Student_Yes','Current_Student_No','I_Understand_chk','Current_Education_chk','Local_Care_chk','Staff_Cwhls_chk','Sub_Film')
VALUES
('$_POST[surname_add]','$_POST[forename_add]','$_POST[dob_add]','$_POST[hometele_add]','$_POST[mobiletele_add]','$_POST[homeadd_add]','$_POST[siblings_add]','$_POST[schoolname_add]','$_POST[headname_add]','$_POST[schooladd_add]','$_POST[schooltele_add]','$_POST[schoolem_add]','$_POST[alevel_add]','$_POST[personstate_add]','$_POST[nameprovided_add]','$_POST[pe_add]','$_POST[se_add]','$_POST[PredGrade_Art]','$_POST[PredGrade_AScience]','$_POST[PredGrade_BusStudies]','$_POST[PredGrade_Electronics]','$_POST[PredGrade_EnglishLang]','$_POST[PredGrade_EnglishLit]','$_POST[PredGrade_French]','$_POST[PredGrade_German]','$_POST[PredGrade_Geog]','$_POST[PredGrade_Graphics]','$_POST[PredGrade_History]','$_POST[PredGrade_Maths]','$_POST[PredGrade_SepScience]','$_POST[PredGrade_ProductDesign]','$_POST[PredGrade_Spanish]','$_POST[PredGrade_Other]','$_POST[Gender_Male]','$_POST[Gender_Female]','$_POST[Sub_EnglishLit]','$_POST[Sub_Maths]','$_POST[Sub_FurtherMaths]','$_POST[Sub_Biology]','$_POST[Sub_Chemistry]','$_POST[Sub_Physics]','$_POST[Sub_French]','$_POST[Sub_German]','$_POST[Sub_Spanish]','$_POST[Sub_Geography]','$_POST[Sub_History]','$_POST[Sub_RE]','$_POST[Sub_FineArt]','$_POST[Sub_Business]','$_POST[Sub_Computing]','$_POST[Sub_GlobPersp]','$_POST[Sub_DramaAndTheatre]','$_POST[Sub_PE]','$_POST[Sub_Dance]','$_POST[Sub_Politics]','$_POST[Sub_Psychology]','$_POST[Sub_Sociology]','$_POST[readprospect_chk]','$_POST[Sib_Yes]','$_POST[Sib_No]','$_POST[Current_Student_Yes]','$_POST[Current_Student_No]','$_POST[I_Understand_chk]','$_POST[Current_Education_chk]','$_POST[Local_Care_chk]','$_POST[Staff_Cwhls_chk]','$_POST[Sub_Film]')";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
?>
<?php
//if "email" variable is filled out, send email
if (isset($_REQUEST['pe_add'])) {
//Email information
$admin_email = $_REQUEST['pe_add'];
$forename = $_REQUEST['forename_add'];
$email = "autoreply@testing.com";
$subject = "Application";
$desc =
"Dear $forename
Thank you for submitting your online application, we will be in touch shortly.
"
;
//send email
mail($admin_email, "$subject", "$desc", "From:" . $email);
//Email response
echo "Thank you for contacting us!";
}
//if "email" variable is not filled out, display the form
else {
?>
If you are seeing this, you need to go back and fill out the Personal Email section!
<?php
}
header("location:complete.php");
mysql_close($con)
?>
Thanks in advance.
Parse error: syntax error, unexpected T_STRING in C:\xampp\htdocs\mywork\unique.php on line 15 <html> <head> <title> </title> </head> <body bgproperties="fixed"> <?php $dbhost = 'localhost'; $dbuser = 'root'; $dbpass = ''; $con = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql'); $dbname = 'mywork'; mysql_select_db($dbname, $con); $sql=mysql_query(insert into users (regno,name,gender,date,month,year,emailid,cell,paddress,caddress,incometype,incomeamt,dad,fyes,dadocup,mom,myes,momocup,password) VALUES ('$_POST[regno]','$_POST[name]','$_POST[gender]','$_POST[date]','$_POST[month]','$_POST[year]','$_POST[emailid]','$_POST[cell]','$_POST[paddress]','$_POST[caddress]','$_POST[incometype]','$_POST[incomeamt]','$_POST[dad]','$_POST[fyes]','$_POST[dadocup]','$_POST[mom]','$_POST[myes]','$_POST[momocup]','$_POST[password]')"); $sql1=mysql_fetch_array($sql); $result = @mysql_query($SQl1); $result="SELECT * FROM users WHERE regno='$regno'"; while($row = mysql_fetch_array($result)) { //echo $row['regno']."regno<br>"; //echo $row['name']."name<br>"; //echo $row['gender']."gender<br>"; //echo $row['date']."date<br>"; //echo $row['month']."month<br>"; //echo $row['year']."year<br>"; //echo $row['emailid']."emailid<br>"; //echo $row['cell']."cell<br>"; //echo $row['paddress']."paddress<br>"; //echo $row['caddress']."caddress<br>"; //echo $row['incometype']."incometype<br>"; //echo $row['incomeamt']."incomeamt<br>"; //echo $row['dad']."dad<br>"; //echo $row['fyes']."fyes<br>"; //echo $row['dadocup']."dadocup<br>"; //echo $row['mom']."mom<br>"; //echo $row['myes']."myes<br>"; //echo $row['momocup']."momocup<br>"; //echo $row['password']."password<br>"; } echo "Thanks for Register!"; if (!mysql_query($sql,$con)) { die('Error: ' . mysql_error()); } echo "1 record added"; mysql_close($con); ?> <form name="security" action="index.php" method="post"> <input type="submit" value="click here to login"> </form> </body> </html> Hi guys,
I am having a hard time trying to figure out why this gives me an error?
#1064 - 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 10
/**
* Baobab (an implementation of Nested Set Model)
*
* Copyright 2010 Riccardo Attilio Galli <riccardo@sideralis.org> [http://www.sideralis.org]
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
/**
* .. note::
* Each occurrence of the word "GENERIC" across this file is meant to be
* replaced with the name of the tree (which must be a valid string to use
* as SQL table name).
*/
/* ############################### */
/* ###### TABLES AND VIEWS ####### */
/* ############################### */
CREATE TABLE IF NOT EXISTS GENERIC (
tree_id INTEGER UNSIGNED NOT NULL,
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
lft INTEGER NOT NULL CHECK (lft > 0),
rgt INTEGER NOT NULL CHECK (rgt > 1),
INDEX(tree_id),
INDEX(lft),
CONSTRAINT order_okay CHECK (lft < rgt)
) ENGINE INNODB;
DROP VIEW IF EXISTS GENERIC_AdjTree;
CREATE VIEW GENERIC_AdjTree (tree_id,parent,child,lft)
AS
SELECT E.tree_id,B.id, E.id, E.lft
FROM GENERIC AS E
LEFT OUTER JOIN GENERIC AS B
ON B.lft = ( SELECT MAX(lft)
FROM GENERIC AS S
WHERE E.lft > S.lft
AND E.lft < S.rgt
AND E.tree_id=S.tree_id)
AND B.tree_id=E.tree_id
ORDER BY lft ASC;
/* ##### LIST OF TREE NAMES IN USE ##### */
CREATE TABLE IF NOT EXISTS Baobab_ForestsNames (
name VARCHAR(200) PRIMARY KEY
) ENGINE INNODB DEFAULT CHARSET=utf8;
INSERT INTO Baobab_ForestsNames(name) VALUES ('GENERIC')
ON DUPLICATE KEY UPDATE name=name;
/* ##################################### */
/* ########################### */
/* ###### ERRORS CONTROL ##### */
/* ########################### */
CREATE TABLE IF NOT EXISTS Baobab_Errors (
code INTEGER UNSIGNED NOT NULL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
msg TINYTEXT NOT NULL,
CONSTRAINT unique_codename UNIQUE (name)
) ENGINE INNODB;
INSERT INTO Baobab_Errors(code,name,msg)
VALUES
(1000,'VERSION','1.3.1'),
(1100,'ROOT_ERROR','Cannot add or move a node next to root'),
(1200,'CHILD_OF_YOURSELF_ERROR','Cannot move a node inside his own subtree'),
(1300,'INDEX_OUT_OF_RANGE','The index is out of range'),
(1400,'NODE_DOES_NOT_EXIST',"Node doesn't exist"),
(1500,'VERSION_NOT_MATCH',"The library and the sql schema have different versions")
ON DUPLICATE KEY UPDATE code=code,name=name,msg=msg;
DROP FUNCTION IF EXISTS Baobab_getErrCode;
CREATE FUNCTION Baobab_getErrCode(x TINYTEXT) RETURNS INT
DETERMINISTIC
RETURN (SELECT code from Baobab_Errors WHERE name=x);
/* ########################## */
/* ######## DROP TREE ####### */
/* ########################## */
DROP PROCEDURE IF EXISTS Baobab_GENERIC_DropTree;
CREATE PROCEDURE Baobab_GENERIC_DropTree (
IN node INTEGER UNSIGNED,
IN update_numbers INTEGER)
LANGUAGE SQL
DETERMINISTIC
MODIFIES SQL DATA
BEGIN
DECLARE drop_tree_id INTEGER UNSIGNED;
DECLARE drop_id INTEGER UNSIGNED;
DECLARE drop_lft INTEGER UNSIGNED;
DECLARE drop_rgt INTEGER UNSIGNED;
/*
declare exit handler for not found rollback;
declare exit handler for sqlexception rollback;
declare exit handler for sqlwarning rollback;
*/
/* save the dropped subtree data with a singleton SELECT */
START TRANSACTION;
/* save the dropped subtree data with a singleton SELECT */
SELECT tree_id, id, lft, rgt
INTO drop_tree_id, drop_id, drop_lft, drop_rgt
FROM GENERIC
WHERE id = node;
/* subtree deletion is easy */
DELETE FROM GENERIC
WHERE tree_id=drop_tree_id AND lft BETWEEN drop_lft and drop_rgt;
IF update_numbers = 1 THEN
/* close up the gap left by the subtree */
UPDATE GENERIC
SET lft = CASE WHEN lft > drop_lft
THEN lft - (drop_rgt - drop_lft + 1)
ELSE lft END,
rgt = CASE WHEN rgt > drop_lft
THEN rgt - (drop_rgt - drop_lft + 1)
ELSE rgt END
WHERE tree_id=drop_tree_id AND (lft > drop_lft OR rgt > drop_lft);
END IF;
COMMIT;
END;
/* ########################## */
/* ###### APPEND CHILD ###### */
/* ########################## */
/* Add a new child to a parent as last sibling
If parent_id is 0, insert a new root node, moving the
previous root (if any) as his child.
If choosen_tree is 0, use the first available integer as id.
If choosen_tree is not present as tree_id in the table, it is used.
*/
DROP PROCEDURE IF EXISTS Baobab_GENERIC_AppendChild;
CREATE PROCEDURE Baobab_GENERIC_AppendChild(
IN choosen_tree INTEGER UNSIGNED,
IN parent_id INTEGER UNSIGNED,
OUT new_id INTEGER UNSIGNED,
OUT cur_tree_id INTEGER UNSIGNED)
LANGUAGE SQL
DETERMINISTIC
BEGIN
DECLARE num INTEGER UNSIGNED;
START TRANSACTION;
SET cur_tree_id = IF(choosen_tree > 0,
choosen_tree,
IFNULL((SELECT MAX(tree_id)+1 FROM GENERIC),1)
);
IF parent_id = 0 THEN /* inserting a new root node*/
UPDATE GENERIC
SET lft = lft+1, rgt = rgt+1
WHERE tree_id=cur_tree_id;
SET num = IFNULL((SELECT MAX(rgt)+1 FROM GENERIC WHERE tree_id=cur_tree_id),2);
INSERT INTO GENERIC(tree_id, id, lft, rgt)
VALUES (cur_tree_id, NULL, 1, num);
ELSE /* append a new node as last right child of his parent */
SET num = (SELECT rgt
FROM GENERIC
WHERE id = parent_id
);
UPDATE GENERIC
SET lft = CASE WHEN lft > num
THEN lft + 2
ELSE lft END,
rgt = CASE WHEN rgt >= num
THEN rgt + 2
ELSE rgt END
WHERE tree_id=cur_tree_id AND rgt >= num;
INSERT INTO GENERIC(tree_id, id, lft, rgt)
VALUES (cur_tree_id,NULL, num, (num + 1));
END IF;
SELECT LAST_INSERT_ID() INTO new_id;
COMMIT;
END;
/* ############################### */
/* ###### INSERT NODE AFTER ###### */
/* ############################### */
DROP PROCEDURE IF EXISTS Baobab_GENERIC_insertAfter;
CREATE PROCEDURE Baobab_GENERIC_insertAfter(
IN sibling_id INTEGER UNSIGNED,
OUT new_id INTEGER UNSIGNED,
OUT error_code INTEGER UNSIGNED)
LANGUAGE SQL
DETERMINISTIC
main:BEGIN
IF 1 = (SELECT lft FROM GENERIC WHERE id = sibling_id) THEN
BEGIN
SELECT Baobab_getErrCode('ROOT_ERROR') INTO error_code;
LEAVE main;
END;
ELSE
BEGIN
DECLARE lft_sibling INTEGER UNSIGNED;
DECLARE choosen_tree INTEGER UNSIGNED;
START TRANSACTION;
SELECT tree_id,rgt
INTO choosen_tree,lft_sibling
FROM GENERIC
WHERE id = sibling_id;
IF ISNULL(lft_sibling) THEN
BEGIN
SELECT Baobab_getErrCode('NODE_DOES_NOT_EXIST') INTO error_code;
LEAVE main;
END;
END IF;
UPDATE GENERIC
SET lft = CASE WHEN lft < lft_sibling
THEN lft
ELSE lft + 2 END,
rgt = CASE WHEN rgt < lft_sibling
THEN rgt
ELSE rgt + 2 END
WHERE tree_id=choosen_tree AND rgt > lft_sibling;
INSERT INTO GENERIC(tree_id,id,lft,rgt)
VALUES (choosen_tree,NULL, (lft_sibling + 1),(lft_sibling + 2));
SELECT LAST_INSERT_ID() INTO new_id;
COMMIT;
END;
END IF;
END;
/* ################################ */
/* ###### INSERT NODE BEFORE ###### */
/* ################################ */
DROP PROCEDURE IF EXISTS Baobab_GENERIC_insertBefore;
CREATE PROCEDURE Baobab_GENERIC_insertBefore(
IN sibling_id INTEGER UNSIGNED,
OUT new_id INTEGER UNSIGNED,
OUT error_code INTEGER UNSIGNED)
LANGUAGE SQL
DETERMINISTIC
main:BEGIN
IF 1 = (SELECT lft FROM GENERIC WHERE id = sibling_id) THEN
BEGIN
SELECT Baobab_getErrCode('ROOT_ERROR') INTO error_code;
LEAVE main;
END;
ELSE
BEGIN
DECLARE rgt_sibling INTEGER UNSIGNED;
DECLARE choosen_tree INTEGER UNSIGNED;
START TRANSACTION;
SELECT tree_id,lft
INTO choosen_tree,rgt_sibling
FROM GENERIC
WHERE id = sibling_id;
IF ISNULL(rgt_sibling) THEN
BEGIN
SELECT Baobab_getErrCode('NODE_DOES_NOT_EXIST') INTO error_code;
LEAVE main;
END;
END IF;
UPDATE IGNORE GENERIC
SET lft = CASE WHEN lft < rgt_sibling
THEN lft
ELSE lft + 2 END,
rgt = CASE WHEN rgt < rgt_sibling
THEN rgt
ELSE rgt + 2 END
WHERE tree_id=choosen_tree AND rgt >= rgt_sibling
ORDER BY lft DESC; /* order by is meant to avoid uniqueness violation on update */
INSERT INTO GENERIC(tree_id,id,lft,rgt)
VALUES (choosen_tree,NULL, rgt_sibling, rgt_sibling + 1);
SELECT LAST_INSERT_ID() INTO new_id;
COMMIT;
END;
END IF;
END;
/* ################################### */
/* ###### INSERT CHILD AT INDEX ###### */
/* ################################### */
/* Add a new child to parent 'parent_id' at index 'index'.
index is the new child position, 0 will put the new node as first.
index can be negative, where -1 will put the new node before the last one
*/
DROP PROCEDURE IF EXISTS Baobab_GENERIC_InsertChildAtIndex;
CREATE PROCEDURE Baobab_GENERIC_InsertChildAtIndex(
IN parent_id INTEGER UNSIGNED,
IN idx INTEGER,
OUT new_id INTEGER UNSIGNED,
OUT error_code INTEGER UNSIGNED)
LANGUAGE SQL
DETERMINISTIC
BEGIN
DECLARE nth_child INTEGER UNSIGNED;
DECLARE cur_tree_id INTEGER UNSIGNED;
SET error_code=0;
SET new_id=0;
CALL Baobab_GENERIC_getNthChild(parent_id,idx,nth_child,error_code);
IF NOT error_code THEN
CALL Baobab_GENERIC_insertBefore(nth_child,new_id,error_code);
ELSE IF idx = 0 AND error_code = (SELECT Baobab_getErrCode('INDEX_OUT_OF_RANGE')) THEN
BEGIN
SET error_code = 0;
CALL Baobab_GENERIC_AppendChild((SELECT tree_id FROM GENERIC WHERE id = parent_id),
parent_id,
new_id,
cur_tree_id);
END;
END IF;
END IF;
END;
/* ########################### */
/* ###### GET NTH CHILD ###### */
/* ########################### */
DROP PROCEDURE IF EXISTS Baobab_GENERIC_getNthChild;
CREATE PROCEDURE Baobab_GENERIC_getNthChild(
IN parent_id INTEGER UNSIGNED,
IN idx INTEGER,
OUT nth_child INTEGER UNSIGNED,
OUT error_code INTEGER UNSIGNED)
LANGUAGE SQL
DETERMINISTIC
main:BEGIN
DECLARE num_children INTEGER;
SET error_code=0;
SELECT COUNT(*)
INTO num_children
FROM GENERIC_AdjTree WHERE parent = parent_id;
IF num_children = 0 OR IF(idx<0,(-idx)-1,idx) >= num_children THEN
/* idx is out of range */
BEGIN
SELECT Baobab_getErrCode('INDEX_OUT_OF_RANGE') INTO error_code;
LEAVE main;
END;
ELSE
SELECT child
INTO nth_child
FROM GENERIC_AdjTree as t1
WHERE (SELECT count(*) FROM GENERIC_AdjTree as t2
WHERE parent = parent_id AND t2.lft<=t1.lft AND t1.tree_id=t2.tree_id
)
= (CASE
WHEN idx >= 0
THEN idx+1
ELSE num_children+1+idx
END
)
LIMIT 1;
END IF;
END;
/* ###################################### */
/* ###### MOVE SUBTREE BEFORE NODE ###### */
/* ###################################### */
DROP PROCEDURE IF EXISTS Baobab_GENERIC_MoveSubtreeBefore;
CREATE PROCEDURE Baobab_GENERIC_MoveSubtreeBefore(
IN node_id_to_move INTEGER UNSIGNED,
IN reference_node INTEGER UNSIGNED,
OUT error_code INTEGER UNSIGNED)
LANGUAGE SQL
DETERMINISTIC
main:BEGIN
DECLARE node_revised INTEGER UNSIGNED;
DECLARE move_as_first_sibling BOOLEAN;
DECLARE ref_left INTEGER UNSIGNED;
DECLARE ref_node_tree INTEGER UNSIGNED;
SET error_code=0; /* 0 means no error */
SET move_as_first_sibling = TRUE;
SELECT tree_id,lft
INTO ref_node_tree,ref_left
FROM GENERIC WHERE id = reference_node;
IF ref_left = 1 THEN
BEGIN
/* cannot move a parent node before or after root */
SELECT Baobab_getErrCode('ROOT_ERROR') INTO error_code;
LEAVE main;
END;
END IF;
/* if reference_node is the first child of his parent, set node_revised
to the parent id, else set node_revised to NULL */
SET node_revised = ( SELECT id FROM GENERIC WHERE tree_id=ref_node_tree AND lft = -1+ ref_left);
IF ISNULL(node_revised) THEN /* if node_revised is NULL we must find the previous sibling */
BEGIN
SET node_revised= (SELECT id FROM GENERIC
WHERE tree_id=ref_node_tree AND rgt = -1 + ref_left);
SET move_as_first_sibling = FALSE;
END;
END IF;
CALL Baobab_GENERIC_MoveSubtree_real(
node_id_to_move, node_revised , move_as_first_sibling, error_code
);
END;
/* ##################################### */
/* ###### MOVE SUBTREE AFTER NODE ###### */
/* ##################################### */
DROP PROCEDURE IF EXISTS Baobab_GENERIC_MoveSubtreeAfter;
CREATE PROCEDURE Baobab_GENERIC_MoveSubtreeAfter(
IN node_id_to_move INTEGER UNSIGNED,
IN reference_node INTEGER UNSIGNED,
OUT error_code INTEGER UNSIGNED)
LANGUAGE SQL
DETERMINISTIC
BEGIN
SELECT 0 INTO error_code; /* 0 means no error */
CALL Baobab_GENERIC_MoveSubtree_real(
node_id_to_move,reference_node,FALSE,error_code
);
END;
/* ##################################### */
/* ####### MOVE SUBTREE AT INDEX ####### */
/* ##################################### */
DROP PROCEDURE IF EXISTS Baobab_GENERIC_MoveSubtreeAtIndex;
CREATE PROCEDURE Baobab_GENERIC_MoveSubtreeAtIndex(
IN node_id_to_move INTEGER UNSIGNED,
IN parent_id INTEGER UNSIGNED,
IN idx INTEGER,
OUT error_code INTEGER)
LANGUAGE SQL
DETERMINISTIC
main:BEGIN
DECLARE nth_child INTEGER UNSIGNED;
DECLARE num_children INTEGER;
DECLARE parent_of_node_to_move INTEGER UNSIGNED;
DECLARE s_lft INTEGER UNSIGNED;
DECLARE current_idx INTEGER;
SET error_code=0;
SELECT COUNT(*)
INTO num_children
FROM GENERIC_AdjTree WHERE parent = parent_id;
IF idx < 0 THEN
SET idx = num_children + idx;
ELSEIF idx > 0 THEN BEGIN
SELECT parent, lft
INTO parent_of_node_to_move, s_lft
FROM GENERIC_AdjTree WHERE child = node_id_to_move;
IF parent_of_node_to_move = parent_id THEN BEGIN
SELECT count(*)
INTO current_idx
FROM GENERIC_AdjTree
WHERE parent = parent_id AND lft < s_lft;
IF idx > current_idx THEN
SET idx = idx + 1;
END IF;
END;
END IF;
END;
END IF;
SET idx = IF(idx<0,num_children+idx,idx);
IF idx = 0 THEN /* moving as first child, special case */
CALL Baobab_GENERIC_MoveSubtree_real(node_id_to_move,parent_id,TRUE,error_code);
ELSE
BEGIN
/* search the node before idx, and we wil move our node after that */
CALL Baobab_GENERIC_getNthChild(parent_id,idx-1,nth_child,error_code);
IF NOT error_code THEN
CALL Baobab_GENERIC_MoveSubtree_real(node_id_to_move,nth_child,FALSE,error_code);
END IF;
END;
END IF;
END;
/* ####################################### */
/* ####### MOVE SUBTREE REAL LOGIC #######*/
/* ####################################### */
/* If move_as_first_sibling is FALSE, move node_id_to_move after reference_node,
else reference_node is the new father of node_id_to_move */
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_UNSIGNED_SUBTRACTION';
DROP PROCEDURE IF EXISTS Baobab_GENERIC_MoveSubtree_real;
CREATE PROCEDURE Baobab_GENERIC_MoveSubtree_real(
IN node_id_to_move INTEGER UNSIGNED,
IN reference_node INTEGER UNSIGNED,
IN move_as_first_sibling BOOLEAN,
OUT error_code INTEGER
)
LANGUAGE SQL
DETERMINISTIC
main:BEGIN
DECLARE s_lft INTEGER UNSIGNED;
DECLARE s_rgt INTEGER UNSIGNED;
DECLARE ref_lft INTEGER UNSIGNED;
DECLARE ref_rgt INTEGER UNSIGNED;
DECLARE source_node_tree INTEGER UNSIGNED;
DECLARE ref_node_tree INTEGER UNSIGNED;
DECLARE diff_when_inside_sourcetree BIGINT SIGNED;
DECLARE diff_when_next_sourcetree BIGINT SIGNED;
DECLARE ext_bound_1 INTEGER UNSIGNED;
DECLARE ext_bound_2 INTEGER UNSIGNED;
SET error_code=0;
START TRANSACTION;
/* select tree, left and right of the node to move */
SELECT tree_id,lft, rgt
INTO source_node_tree, s_lft, s_rgt
FROM GENERIC
WHERE id = node_id_to_move;
/* select left and right of the reference node
If moving as first sibling, ref_lft will become the new lft value of node_id_to_move,
(and ref_rgt is unused), else we're saving left and right value of soon to be
previous sibling
*/
SELECT tree_id, IF(move_as_first_sibling,lft+1,lft), rgt
INTO ref_node_tree, ref_lft, ref_rgt
FROM GENERIC
WHERE id = reference_node;
IF move_as_first_sibling = TRUE THEN
IF s_lft <= ref_lft AND s_rgt >= ref_rgt AND source_node_tree=ref_node_tree THEN
/* cannot move a parent node inside his own subtree */
BEGIN
SELECT Baobab_getErrCode('CHILD_OF_YOURSELF_ERROR') INTO error_code;
LEAVE main;
END;
ELSE
IF s_lft > ref_lft THEN BEGIN
SET diff_when_inside_sourcetree = -(s_lft-ref_lft);
SET diff_when_next_sourcetree = s_rgt-s_lft+1;
SET ext_bound_1 = ref_lft;
SET ext_bound_2 = s_lft-1;
END;
ELSEIF s_lft = ref_lft and source_node_tree = ref_node_tree THEN BEGIN
/* we have been asked to move a node to his same position */
LEAVE main;
END;
ELSE BEGIN
SET diff_when_inside_sourcetree = ref_lft-s_rgt-1;
SET diff_when_next_sourcetree = -(s_rgt-s_lft+1);
SET ext_bound_1 = s_rgt+1;
SET ext_bound_2 = ref_lft-1;
END;
END IF;
END IF;
ELSE /* moving after an existing child */
IF ref_lft = 1 THEN /* cannot move a node before or after root */
BEGIN
SELECT Baobab_getErrCode('ROOT_ERROR') INTO error_code;
LEAVE main;
END;
ELSEIF s_lft < ref_lft AND s_rgt > ref_rgt AND source_node_tree=ref_node_tree THEN
/* cannot move a parent node inside his own subtree */
BEGIN
SELECT Baobab_getErrCode('CHILD_OF_YOURSELF_ERROR') INTO error_code;
LEAVE main;
END;
ELSE
IF s_lft > ref_rgt THEN BEGIN
SET diff_when_inside_sourcetree = -(s_lft-ref_rgt-1);
SET diff_when_next_sourcetree = s_rgt-s_lft+1;
SET ext_bound_1 = ref_rgt+1;
SET ext_bound_2 = s_lft-1;
END;
ELSE BEGIN
SET diff_when_inside_sourcetree = ref_rgt-s_rgt;
SET diff_when_next_sourcetree = -(s_rgt-s_lft+1);
SET ext_bound_1 = s_rgt+1;
SET ext_bound_2 = ref_rgt;
END;
END IF;
END IF;
END IF;
IF source_node_tree <> ref_node_tree THEN
BEGIN
CALL Baobab_GENERIC_MoveSubtree_Different_Trees(
node_id_to_move,reference_node,move_as_first_sibling);
LEAVE main;
END;
END IF;
UPDATE GENERIC
SET lft =
lft + CASE
WHEN lft BETWEEN s_lft AND s_rgt
THEN diff_when_inside_sourcetree
WHEN lft BETWEEN ext_bound_1 AND ext_bound_2
THEN diff_when_next_sourcetree
ELSE 0 END
,
rgt =
rgt + CASE
WHEN rgt BETWEEN s_lft AND s_rgt
THEN diff_when_inside_sourcetree
WHEN rgt BETWEEN ext_bound_1 AND ext_bound_2
THEN diff_when_next_sourcetree
ELSE 0 END
WHERE tree_id=source_node_tree;
COMMIT;
END;
SET sql_mode=@OLD_SQL_MODE;
DROP PROCEDURE IF EXISTS Baobab_GENERIC_MoveSubtree_Different_Trees;
CREATE PROCEDURE Baobab_GENERIC_MoveSubtree_Different_Trees(
IN node_id_to_move INTEGER UNSIGNED,
IN reference_node INTEGER UNSIGNED,
IN move_as_first_sibling BOOLEAN
)
LANGUAGE SQL
DETERMINISTIC
main:BEGIN
DECLARE s_lft INTEGER UNSIGNED;
DECLARE s_rgt INTEGER UNSIGNED;
DECLARE ref_lft INTEGER UNSIGNED;
DECLARE ref_rgt INTEGER UNSIGNED;
DECLARE source_node_tree INTEGER UNSIGNED;
DECLARE ref_node_tree INTEGER UNSIGNED;
START TRANSACTION;
/* select tree, left and right of the node to move */
SELECT tree_id,lft, rgt
INTO source_node_tree, s_lft, s_rgt
FROM GENERIC
WHERE id = node_id_to_move;
/* The current select will behave differently whether we're moving
the node as first sibling or not.
If move_as_first_sibling,
ref_lft will have the value of the "lft" field of node_id_to_move at end
of move (ref_rgt here is discarded)
else
ref_lft and ref_rgt will have the values of the node before node_id_to_move
at end of move
*/
SELECT tree_id, IF(move_as_first_sibling,lft+1,lft), rgt
INTO ref_node_tree, ref_lft, ref_rgt
FROM GENERIC
WHERE id = reference_node;
IF (move_as_first_sibling) THEN BEGIN
/* create a gap in the destination tree to hold the subtree */
UPDATE GENERIC
SET lft = CASE WHEN lft < ref_lft
THEN lft
ELSE lft + s_rgt-s_lft+1 END,
rgt = CASE WHEN rgt < ref_lft
THEN rgt
ELSE rgt + s_rgt-s_lft+1 END
WHERE tree_id=ref_node_tree AND rgt >= ref_lft;
/* move the subtree to the new tree */
UPDATE GENERIC
SET lft = ref_lft + (lft-s_lft),
rgt = ref_lft + (rgt-s_lft),
tree_id = ref_node_tree
WHERE tree_id = source_node_tree AND lft >= s_lft AND rgt <= s_rgt;
END;
ELSE BEGIN
/* create a gap in the destination tree to hold the subtree */
UPDATE GENERIC
SET lft = CASE WHEN lft < ref_rgt
THEN lft
ELSE lft + s_rgt-s_lft+1 END,
rgt = CASE WHEN rgt <= ref_rgt
THEN rgt
ELSE rgt + s_rgt-s_lft+1 END
WHERE tree_id=ref_node_tree AND rgt > ref_rgt;
/* move the subtree to the new tree */
UPDATE GENERIC
SET lft = ref_rgt+1 + (lft-s_lft),
rgt = ref_rgt+1 + (rgt-s_lft),
tree_id = ref_node_tree
WHERE tree_id = source_node_tree AND lft >= s_lft AND rgt <= s_rgt;
END;
END IF;
/* close the gap in the source tree */
CALL Baobab_GENERIC_Close_Gaps(source_node_tree);
COMMIT;
END;
/* ########################## */
/* ####### CLOSE GAPS ####### */
/* ########################## */
DROP PROCEDURE IF EXISTS Baobab_GENERIC_Close_Gaps;
CREATE PROCEDURE Baobab_GENERIC_Close_Gaps(
IN choosen_tree INTEGER UNSIGNED)
LANGUAGE SQL
DETERMINISTIC
BEGIN
UPDATE GENERIC
SET lft = (SELECT COUNT(*)
FROM (
SELECT lft as seq_nbr FROM GENERIC WHERE tree_id=choosen_tree
UNION ALL
SELECT rgt FROM GENERIC WHERE tree_id=choosen_tree
) AS LftRgt
WHERE tree_id=choosen_tree AND seq_nbr <= lft
),
rgt = (SELECT COUNT(*)
FROM (
SELECT lft as seq_nbr FROM GENERIC WHERE tree_id=choosen_tree
UNION ALL
SELECT rgt FROM GENERIC WHERE tree_id=choosen_tree
) AS LftRgt
WHERE tree_id=choosen_tree AND seq_nbr <= rgt
)
WHERE tree_id=choosen_tree;
END
help - can you tell me why this is giving the following error
#1064 - 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 'DROP TABLE IF EXISTS `admin`' at line 8
i am trying to import a data file ... first few lines of which are
-- MySQL dump 10.10 -- -- Host: localhost Database: bromleyone -- ------------------------------------------------------ -- Server version 5.0.22-Debian_0ubuntu6.06.10-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; use bromleyone -- -- Table structure for table `admin` -- DROP TABLE IF EXISTS `admin`; CREATE TABLE `admin` ( `id` int(11) NOT NULL auto_increment, `username` varchar(30) NOT NULL default '', `password` varchar(30) NOT NULL default '', `name` varchar(40) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Hi guys i have the following code which is misbehaving can anyone see where its wrong? Code: [Select] <?php include 'dbc.php'; page_protect(); company(); $Referrer = mysql_query("SELECT * FROM Referrer WHERE SentOut='0' "); if (isset($_POST['submit'])) { //Assign each array to a variable $StaffMember = $_POST['StaffMember']; $referrer = $_POST['referrer']; $referred = $_POST['referred']; $SentOut = $_POST['SentOut']; $today = date("y.m.d H:i:s"); $user_id = $_SESSION['user_id']; $IssueNum = $_POST['Referrerid']; $limit = count($StaffMember); for($k=0;$k<$limit;$k++){ $msg[] = "$limit New KPI's Added"; $values[$k] = array( $StaffMember[$k],$referrer[$k],$referred[$k],$SentOut[$k],$today,$user_id ); // build the array of values for the query string } foreach( $values as $key => $value ) { $query = "UPDATE `Referrer` (StaffMember, referer, referred, SentOut, SentOutDate, SentOutBy) VALUES ('" . implode( '\', \'', $value ) . "') WHERE IssueNum= '{$IssueNum[$key]}'"; mysql_query($query) or die(mysql_error()); } } if (checkAdmin()) { ?> <html> <head> <title>Book Off Holiday</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <script src="php_calendar/scripts.js" type="text/javascript"></script> <link href="styles.css" rel="stylesheet" type="text/css"> </head> <body> <form name="form" action="SendReferrers.php" method="post"> <table width="100%" border="0" cellspacing="0" cellpadding="5" class="main"> <tr> <td colspan="3"> </td> </tr> <td width="160" valign="top"> <?php if (isset($_SESSION['user_id'])) { } ?> <a href="admin.php">Admin CP </a> </td> <td width="732" valign="top"> <p> <h3 class="titlehdr">New KPI</h3> <table width="300px" border="0" align="Centre" cellpadding="2" cellspacing="0"> <tr bgcolor="#000050"> <td width="20px"><h3 class="Text2">Referrer ID</h3></td> <td width="20px"><h3 class="Text2">Staff Member</h3></td> <td width="20px"><h3 class="Text2">referrer</h3></td> <td width="20px"><h3 class="Text2">referred</h3></td> <td width="40px"><h3 class="Text2">Sent Out</h3></td> </tr> <?php while ($rrows = mysql_fetch_array($Referrer)) {?> <tr> <td><h3 class="Text3"><input type="" name="Referrerid[]" id="Referrerid[]" size="4" value="<?php echo $rrows['IssueNum'];?>" /></h3></td> <td><h3 class="Text3"><input type="" name="StaffMember[]" id="StaffMember[]" size="4" value="<?php echo $rrows['StaffMember'];?>" /></h3></td> <td><h3 class="Text3"><input type="" name="referrer[]" id="referrer[]" value="<?php echo $rrows['referer'];?>" /></h3></td> <td><h3 class="Text3"><input type="" name="referred[]" id="referred[]" value="<?php echo $rrows['referred'];?>" /></h3></td> <td><h3 class="Text3"><input name="SentOut[]" type="checkbox" value="1" id="SentOut[]"></h3></td> </tr> <?php } ?> </table> <input name="submit" type="submit" id="submit" value="Create"> </table> </form> </body> </html> <?php } ?> the error i get when the submit button is clicked is 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 '(StaffMember, referer, referred, SentOut, SentOutDate, SentOutBy) VALUES' at line 1 any help would be appriciative Howdy folks, I am creating a Facebook app for a bit of fun and practice and getting the following error in index.php: Code: [Select] Invalid query -- SELECT * FROM `results` WHERE `resultLow` <= AND `resultHigh`>= -- 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 'AND `resultHigh`>=' at line 1 Here is the area: Code: [Select] $res = query("SELECT * FROM `results` WHERE `resultLow` <=$user_score AND `resultHigh`>=$user_score"); Any help would be appreciated. Hey all, I keep getting this 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 '' at line 2 When I use the script below. I'm finding it a bit confusing because everything about it continues to work, it's just it gives me an error. When the script runs, the outcome is "Success! Your dog now looks far more energetic! Looks like this food is all used up." Followed by the error, which cuts the remainder of the page off. Does anybody know why it's doing this? $dogyay = $_POST['dogid']; $checkenergy = "SELECT energy FROM dogs WHERE id=$dogyay"; $energylevel = mysql_query($checkenergy) or die(mysql_error()); $row = mysql_fetch_array($energylevel) or die(mysql_error()); if($row['energy'] >= 100) { echo "<b>Oops!</b> Looks like your dog is full right now...";} else{ echo "<b>Success! Your dog now looks far more energetic!</b><br><br>"; $sql11="UPDATE dogs SET energy=energy + 50 WHERE id=$dogyay"; $result11=mysql_query($sql11); $sql12="UPDATE items SET uses = uses - 1 WHERE itemid=$id"; $result12=mysql_query($sql12); $checkuses = "SELECT uses FROM items WHERE itemid=$id"; $useslevel = mysql_query($checkuses) or die(mysql_error()); $row = mysql_fetch_array($useslevel) or die(mysql_error()); if($row['uses'] == 0) { echo "Looks like this food is all used up.<bR><br>"; mysql_query("DELETE FROM items WHERE itemid='$id'") or die(mysql_error());} Thanks ! Guys, I'm not at all able to insert characters like " ' ` and all sorts into my database or it will always return an error. I just created a textarea field in my site of which I just want to istore all those collected datas into my database for later retrieval and all sorts. Please help! This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=352460.0 Code: [Select] <?php mysql_connect ("-","-","-") or die ('Error'); mysql_select_db ("-"); $out = mysql_query("SELECT * FROM guestbook ORDER BY id DESC"); while($row = mysql_fetch_assoc($out); --and this one if that braces is deleted { ----this is where im getting the error $name = $row['name']; $email = $row['email']; $txt = $row['comment']; $msg = "Are you sure you want to delete"; /* @var $_REQUEST <type> */ if (isset($_REQUEST ["action"]) && $_REQUEST["action"] == "del") { $id = intval($_REQUEST['id']); mysql_query("DELETE FROM guestbook WHERE id=$id;"); echo "<action=index.php>"; } echo "<font face='verdana' size='1'>"; echo "<table border='0'> <tr><td>Name: ".$name."</td></tr>"." <tr><td>Email: ".$email."</td></tr> <tr><td colspan='2'>Comment:</td></tr> <tr><td colspan='2' width='500'><b>".$txt."</b></td></tr> <tr><td><a onclick=\"return confirm('.$msg.');\" href='index.php?action=del&id=".$row['id']."'><span class='red'>["."Delete"."]</span></a> </td></tr> </table><br />"; echo "<hr size='1' width='500' align='left'></font>"; } ?> Kindly help me please. When i delete ({) the error will become the ( i dont know what to do already. Thanks. Is there any visible error in my code? Code: [Select] if (isset($_POST['country'], $_POST['state'], $_POST['city'])) { if ($_POST['state'] = '') { $details = $_POST['city'].', '.$_POST['country']; update_user_location($details); } else { $details = $_POST['city'].', '.$_POST['state']; update_user_location($details); } } Code: [Select] function update_user_location($details) { global $user_info; $details = mres($details); mysql_query("INSERT INTO `user_actions` (`user_id`, `action_id`, `time`, `details`) VALUES (${user_info['uid']}, 1, NOW(), {$details})"); } This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=342913.0 |
|||||||