querying mysql database using php

Question

I've searched google and on here and some info has been useful but mostly not so useful and still can't manage to query my database using php. I'm very new to coding so thought it best to find an answer based on my code rather than other peoples slightly different problems. I have a small database, people fill in a form who wish to hire a bus. i have created a calendar and wish to print out for each day what time and for how long there is a hire, if there is one, based on the data people have entered which has been sent to my database. The whole page shows but the calendar contains an error within its table saying:

"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 ''2013-03-01' at line 1"

even if i delete the "where date=.." part of the query it continues to show this. ive tried several different methods of writing the code out, including a loop and not including one but im not really sure what im meant to do.

EDIT: MY CODE IS NOW:

<?php
$con = mysql_connect("localhost","user","password");

$result = mysql_query("SELECT time, length FROM hire WHERE date='01-03-13'");
$row = mysql_fetch_assoc($result);

$Time = $row['TIME'];
$Length = $row['LENGTH'];

echo "Time: " . $TIME . "<br/>";
echo "Length: " . $LENGTH . "<br/>";


?>

I now get the error "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\Users\Laura\Documents\COMPY\server\www\calendar.php on line 123" It shows the page, calendar and "time" and "length" but with the error and no data

EDIT

<?php
$con = mysql_connect("localhost","user","password");
mysql_select_db("busassociation", $con);                 
$result = mysql_query("SELECT time, length FROM hire WHERE date='01-03-13'");

while ($row = mysql_fetch_assoc($result)){

$time = $row['time'];
$length = $row['length'];

echo "time: " . $time . "<br/>";
echo "length: " . $length;

}
?>

You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). — Quentin, Jan 05 '13 at 20:42

echo_Me · Accepted Answer · 2013-01-05T22:32:20.540

0

use backticks

 $result = mysql_query("SELECT `time`, `length` FROM `hire` WHERE `date`= '2013-03-01' ");

EDIT.

your are fetching your query two times.

 $row = mysql_fetch_row($result);   <------- delete this line

 while( $row = mysql_fetch_row($result) ){

EDIT.2

i think u dont reconize the diference between

mysql_fetch_assoc() and mysql_fetch_row()

if you use mysql_fetch_assoc() it will be correct in your code.

but if u use mysql_fetch_row() as u have done in your code u must echo values like that

 $Time = $row['1']; // suppose that `TIME` is the second column
 $Length = $row['2']; // suppose that `LENGTH` is the third column

EDIT.3

  <?php
 $con = mysql_connect("localhost","user","password");
 mysql_select_db("your_db", $con);                  <---------replace by your database
 $result = mysql_query("SELECT time, length FROM hire WHERE date='01-03-13'");

while ($row = mysql_fetch_assoc($result)){

 $Time = $row['time'];
 $Length = $row['length'];

 echo "Time: " . $Time . "<br/>";
 echo "Length: " . $Length . "<br/>";

 }
 ?>

edited Jan 05 '13 at 22:32

answered Jan 05 '13 at 20:44

echo_Me

37,078
5
58
78

MySQL permits `date` without backticks because so many people used it as a field name. See http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html – MrCode Jan 05 '13 at 20:45
thanks for replies, i restarted services, replaced my code and did what you said $pdo = new PDO('localhost', 'user', 'password'); $statement = $pdo->query("SELECT `time`, `length` FROM hire WHERE `date`='2013-03-01"); $row = $statement->fetch(PDO::FETCH_ASSOC); echo htmlentities($row['time']); echo htmlentities($row['length']); mysql_close($connect); ?>; but i still get the same issue – Lubblobba Jan 05 '13 at 20:58
the $row is beeing fetching the $result two times , so in the second can occur the problem. and the fetch error is always from the sql – echo_Me Jan 05 '13 at 21:01
Didnt LENGTH use to be a mysql built in function not so long ago? :) – Hanky Panky Jan 05 '13 at 21:11
@goodmood What problem can that cause? Be specific, or if you don't actually know what are you doing, don't post an answer, you may mislead somebody. – cypher Jan 05 '13 at 21:17
@HankyPanky I believe that still is a function :) – cypher Jan 05 '13 at 21:18
i stayed with my original code, changed my date format to varchar rather than date, and now have this: "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\Users\Laura\Documents\COMPY\server\www\calendar.php on line 123" i know i should use modern code but i know even less about that than the php code ive been using. – Lubblobba Jan 05 '13 at 21:37
updated my answer . because u use row or fetch . in your code the row is wrong – echo_Me Jan 05 '13 at 21:44
if u have edited your code , please edit your question and paste your new code, maybe im talking in one way and u are in other way – echo_Me Jan 05 '13 at 21:58
u are missing select database function `mysql_select_db("your_db", $con);` and dont use `UPPERCASE` ur query contains `LOWERCASE` columns – echo_Me Jan 05 '13 at 22:14
ah okay no errors any more, thank you! but there is still no data being shown even though i have entered data into the table for the specified date – Lubblobba Jan 05 '13 at 22:26
this is maybe about your `WHERE` clause , u have made wrong date. try remove `WHERE date='01-03-13'` and see if there is data. – echo_Me Jan 05 '13 at 22:30
oh it was due to capilisation. however, after entering another time and length on the same date, i can only show the first piece of data. is there a way to show more than one? if that makes sense – Lubblobba Jan 05 '13 at 22:30
ok if u want more data , then fetch by while loop , look my edited answer number3, i have edited it – echo_Me Jan 05 '13 at 22:32
hmm i have changed my code to what you put however still only have one set of data for that date and the calendar layout has messed up :/ – Lubblobba Jan 05 '13 at 22:39
its about the date . are u sure that this date have many data? try remove this `WHERE` clause and see. – echo_Me Jan 05 '13 at 22:43
i think im far too tired to be doing this, have been making incredibly silly mistakes. sorry to waste your time, but thank you very very much for the help!! – Lubblobba Jan 05 '13 at 22:47

querying mysql database using php

1 Answers1