You have an error in your SQL syntax; check the manual that corresponds to your MySQL

Question

sibymary 0 Newbie Poster

14 Years Ago

hi ,
i got an error.pls help me to correct.
my code is

<form action="return.php" method="post">
                    <table width="200" border="0">
                      <tr>
                        <td><h3 align="center">RETURN</h3></td>
                      </tr>
                    </table>
                    <p align="center">&nbsp;</p>
                 <table width="41%" height="254" border="0">
                   <tbody>
                       <tr>
                         <td width="34%">Author:</td>
                         <td width="66%"><p>
                           <input type="text" name="author1" />
                         </p></td>
                       </tr>
                       <tr>
                         <td>Title:</td>
                         <td><p>
                           <input type="text" name="title1" />
                         </p></td>
                       </tr>
                       <tr>
                         <td>Accession No:</td>
                         <td><p>
                           <input type="text" name="accessno1" />
                         </p></td>
                       </tr>
                       <tr>
                         <td>Name:</td>
                         <td><p>
                           <input type="text" name="name1" />
                         </p></td>
                       </tr>
                       <tr>
                         <td>Register No:</td>
                         <td><p>
                           <input type="text" name="regno1" />
                         </p></td>
                       </tr>
                       <tr>
                         <td>Issue Date:</td>
                         
                         <td><p>
                           <label for="select"></label>
                           <select name="sdate1" id="select">
                             <option>date</option>
                             <option>1</option>
                             <option>2</option>
                             <option>3</option>
                             <option>4</option>
                             <option>5</option>
                             <option>6</option>
                             <option>7</option>
                             <option>8</option>
                             <option>9</option>
                             <option>10</option>
                             <option>11</option>
                             <option>12</option>
                             <option>13</option>
                             <option>14</option>
                             <option>15</option>
                             <option>16</option>
                             <option>17</option>
                             <option>18</option>
                             <option>19</option>
                             <option>20</option>
                             <option>21</option>
                             <option>22</option>
                             <option>23</option>
                             <option>24</option>
                             <option>25</option>
                             <option>26</option>
                             <option>27</option>
                             <option>28</option>
                             <option>29</option>
                             <option>30</option>
                             <option>31</option>
                           </select>
                           <label for="select2"></label>
                           <select name="sdate1" id="select2">
                             <option>month</option>
                             <option>1</option>
                             <option>2</option>
                             <option>3</option>
                             <option>4</option>
                             <option>5</option>
                             <option>6</option>
                             <option>7</option>
                             <option>8</option>
                             <option>9</option>
                             <option>10</option>
                             <option>11</option>
                             <option>12</option>
                           </select>
                           <label for="select3"></label>
                           <select name="sdate1" id="select3">
                             <option>year</option>
                             <option>2007</option>
                             <option>2008</option>
                             <option>2009</option>
                             <option>2010</option>
                             <option>2011</option>
                             <option>2012</option>
                             <option>2013</option>
                             <option>2014</option>
                             <option>2015</option>
                             <option>2016</option>
                           </select>
                         </p></td>
                       </tr>
                       <tr>
                         <td height="34">Due Date:</td>
                         <td><p>
                           <select name="ddate1" id="select4">
                           <option>date</option>
                            <option>1</option>
                          <option>2</option>
                          <option>3</option>
                          <option>4</option>
                          <option>5</option>
                          <option>6</option>
                          <option>7</option>
                          <option>8</option>
                          <option>9</option>
                          <option>10</option>
                          <option>11</option>
                          <option>12</option>
                          <option>13</option>
                          <option>14</option>
                          <option>15</option>
                          <option>16</option>
                          <option>17</option>
                          <option>18</option>
                          <option>19</option>
                          <option>20</option>
                          <option>21</option>
                          <option>22</option>
                          <option>23</option>
                          <option>24</option>
                          <option>25</option>
                          <option>26</option>
                          <option>27</option>
                          <option>28</option>
                          <option>29</option>
                          <option>30</option>
                          <option>31</option>
                           </select>
                           <label for="select5"></label>
                           <select name="ddate1" id="select5">
                           <option>month</option>
                            <option>1</option>
                        <option>2</option>
                        <option>3</option>
                        <option>4</option>
                        <option>5</option>
                        <option>6</option>
                        <option>7</option>
                        <option>8</option>
                        <option>9</option>
                        <option>10</option>
                        <option>11</option>
                        <option>12</option>
                           </select>
                           <label for="select6"></label>
                           <select name="ddate1" id="select6">
                              <option>year</option>
                        <option>2007</option>
                        <option>2008</option>
                        <option>2009</option>
                        <option>2010</option>
                        <option>2011</option>
                        <option>2012</option>
                        <option>2013</option>
                        <option>2014</option>
                        <option>2015</option>
                        <option>2016</option>
                           </select>
                         </p></td>
                       </tr>
                     </tbody>
                   </table>
                
                 <p>&nbsp;</p>
<table width="200" border="0">
            <tr>
              <td>
                <div align="center">
                    <input type="submit" name="button2" id="button2" value="Return" />
                  <input type="reset" name="Reset" id="button" value="Clear" />
                  <input name="bk" value="Back" onClick="javascript:window.history.go(-1)" type="button"> 
                </div>
                         
                <div align="center"></div>
          </td>
            </tr>
            </table>
            </td>

           
          </form>

my php code is

<?php
			
$author1 = $_POST['author1'];
$title1 = $_POST['title1'];
$accessno1=$_POST['accessno1'];
$name1=$_POST['name1'];
$regno1=$_POST['regno1'];
$sdate1=$_POST['sdate1'];
$ddate1=$_POST['ddate1'];
$con = mysql_connect("localhost");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  } 
mysql_select_db("test", $con);
$rs=mysql_query("select * from issue where title='$title1'");
$count=mysql_num_rows($rs);
if($count>0)
{ 
$sql="INSERT INTO return (author1,title1,accessno1,name1,regno1,sdate1,ddate1) VALUES('$author1','$title1','$accessno1','$name1','$regno1','$sdate1','$ddate1')";
mysql_query($sql) or die(mysql_error());
echo "<script ='javascript'type='text/javascript'>
alert('book returned')
</script>";
}

mysql_close($con)
?>

also i have a problem in date in the database..it is displayin like 00-00-0000..how can i recover from this problem

mysql php

3 Contributors
10 Replies
273 Views
2 Days Discussion Span
Latest Post 14 Years Ago Latest Post by karthik_ppts

Dani AI

Generated 9 Months Ago

Good progress so far: was right about the SQL syntax (the table name "return" is a reserved word — either rename the table or wrap the identifier in backticks), and 's tip to print the generated INSERT is a simple way to see what actually goes to MySQL. The remaining issue (dates becoming 0000-00-00) is almost always a data-format or missing-value problem rather than a MySQL bug.

Common causes and what to check

Form field names: make sure the three selects for a date have distinct names (for example sdate1day, sdate1month, sdate1year and the ddate1 equivalents). If they all share the same name only one value gets submitted and PHP warnings like "Undefined index" appear.
Constructed value: MySQL DATE expects a properly formed YYYY-MM-DD string. If you build an empty or malformed string PHP/MySQL will store the zero date. Confirm the PHP variable actually contains the expected value before the INSERT (print/var_dump it or print the SQL).
Column type and names: verify the target columns are really DATE (or DATETIME) and that your INSERT column names exactly match the table schema. A mismatch can cause unexpected conversions.
MySQL warnings: if the INSERT succeeds but the date becomes zero, check server warnings (SHOW WARNINGS) — they explain conversion problems.

Practical validation example (build a zero-padded date from three selects)

if (isset($_POST['sdate1year'], $_POST['sdate1month'], $_POST['sdate1day'])) {
    $sdate1 = sprintf('%04d-%02d-%02d',
        (int)$_POST['sdate1year'],
        (int)$_POST['sdate1month'],
        (int)$_POST['sdate1day']);
} else {
    $sdate1 = null; // handle missing input
}

Final notes: test the exact INSERT you print in phpMyAdmin, check SHOW WARNINGS after running it, and avoid using reserved names. For longer-term safety and to prevent SQL injection, move away from the old mysql_* functions and use prepared statements with mysqli or PDO.

All 10 Replies

karthik_ppts 81 Posting Pro

14 Years Ago

replace your line 20 oh php as

echo $sql="INSERT INTO return (author1,title1,accessno1,name1,regno1,sdate1,ddate1) VALUES('$author1','$title1','$accessno1','$name1','$regno1','$sdate1','$ddate1')";

copy the printed query and execute it in the sql section of your phpmyadmin ans see what happens.

Edited 14 Years Ago by karthik_ppts because: n/a

cereal 1,524 Nearly a Senior Poster

14 Years Ago

And also your table name, return, is a reserved word, check this:
Use backticks if you want to use it:

"insert into `return` ..."

bye :)

karthik_ppts commented: Yes. +6

karthik_ppts 81 Posting Pro

14 Years Ago

change your select box name as day, select2 name as month, and select3 name as year and replace your line 8 of php code as

$sdate1=$_POST['year']."-".;$_POST['month']."-".[$_POST['day']

.

Do the same for other date too.

Edited 14 Years Ago by karthik_ppts because: n/a

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

sibymary 0 Newbie Poster · Answer 1 · 2011-09-20T14:50:44+00:00

thankuu...i got the correct answer...also i hav 1 doubt.in database date is displayin like 00-00-0000.

sibymary 0 Newbie Poster · Answer 2 · 2011-09-20T20:55:39+00:00

after entering the data ,some errors are found.
like

Notice: Undefined index: year in C:\xampp\htdocs\project\return.php on line 16

Notice: Undefined index: month in C:\xampp\htdocs\project\return.php on line 16

Notice: Undefined index: day in C:\xampp\htdocs\project\return.php on line 16

Notice: Undefined index: year in C:\xampp\htdocs\project\return.php on line 17

Notice: Undefined index: month in C:\xampp\htdocs\project\return.php on line 17

Notice: Undefined index: day in C:\xampp\htdocs\project\return.php on line 17
can u clear this problem as soon as possible

sibymary 0 Newbie Poster · Answer 3 · 2011-09-20T20:57:56+00:00

11 $author1 = $_POST['author1'];
12 $title1 = $_POST['title1'];
13 $accessno1=$_POST['accessno1'];
14 $name1=$_POST['name1'];
15 $regno1=$_POST['regno1'];
16 $sdate1=$_POST['year']."-".$_POST['month']."-".$_POST['day'];
17 $ddate1=$_POST['year']."-".$_POST['month']."-".$_POST['day'];

cereal 1,524 Nearly a Senior Poster Featured Poster · Answer 4 · 2011-09-20T23:05:09+00:00

Read what Karthik suggested to you. This happens because you're sending the same name field for day, month and year. An example, change names of select fields with these:

sdate1day
sdate1month
sdate1year
ddate1day
ddate1month
ddate1year

Change also the lines n.16 and 17, so they will be:

$sdate1=$_POST['sdate1year']."-".$_POST['sdate1month']."-".$_POST['sdate1day'];
$ddate1=$_POST['ddate1year']."-".$_POST['ddate1month']."-".$_POST['ddate1day'];

sibymary 0 Newbie Poster · Answer 5 · 2011-09-21T19:50:06+00:00

I have done what u told. But in db, again date is like 0000-00-00......I want to change it to what I’m inserting from form into the database like 2005-3-12

cereal 1,524 Nearly a Senior Poster Featured Poster · Answer 6 · 2011-09-21T19:58:49+00:00

datetime column? Use strtotime():

<?php
$year = '2011';
$month = '9';
$day = '21';
echo date('Y-m-d',strtotime($year.'-'.$month.'-'.$day));
?>

In your case will be:

$sdate1 = date('Y-m-d',strtotime($_POST['sdate1year']."-".$_POST['sdate1month']."-".$_POST['sdate1day']));

bye :)

karthik_ppts 81 Posting Pro · Answer 7 · 2011-09-22T09:43:17+00:00

karthik_ppts 81 Posting Pro

14 Years Ago

post your recent code.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL

Dani AI

Recommended Answers Collapse Answers

All 10 Replies

Recommended Answers