Jump to content

SQL and connecting to database to give output.

yrstruly

By yrstruly
in SQL

Recommended Posts

yrstruly Member

yrstruly

Posted
Posted

HalloCan somebody please help me. I have a login code and it work. And i have an sql which generates the users information out of the database. Right now all i can do is to login and then it give me the user's vehicle registration number, which is correct, but i need more action then that.The question is, how do i write an sql code, thats gives me the vehicle registration number ,car model (all is in database) , data, place km's ect. This should then be generated from the database and be put out in a neat table with 5 rows divided into columns. Can somebody pleae help me?Here is the SQL CODE<?phpsession_start();require_once('config.php');$registration_number = $_REQUEST['username'];$password = $_REQUEST['password'];//Check that password entered is correct and get the clients email address for this vehicle IDsession_start();$enc_password = md5($password);$password_query = "SELECT VE_IDFROM `vehicle` WHERE `VE_Reg_Number` = '$registration_number'AND `VE_Password` = '$enc_password'" ;//echo $password_query;echo "<br>";if(!$password_result = mysql_query($password_query)) { die("Error with the database. Please try again later");}if(mysql_num_rows($password_result)== 0) { die("Password is incorrect. Please try again."); }$row = mysql_fetch_assoc($password_result);$Vehicle_ID = $row['VE_ID'];//echo "Vehicle id is $Vehicle_ID<br>";//Get records for this vehicle_id from the $petrol_query = "SELECT `PE_Place` , `PE_Date_Time` , `PE_Total_KM` , `PE_KM_Travelled` , `PE_Litres_Used` FROM `petrol_entries` WHERE `VE_ID` =$Vehicle_IDORDER BY `petrol_entries`.`PE_Date_Time` ASC ";//echo $petrol_query;echo "<br>";if(!$petrol_result = mysql_query($petrol_query)) { die("Error with the database. Please try again later");}if(mysql_num_rows($petrol_result)== 0) { die("No records exist"); }?>Your vehicle regisration number is <?phpecho "$registration_number";echo "<br />";while($row = mysql_fetch_assoc($petrol_result)) { $number_of_entries ++;// echo "Date: ".$row['PE_Date_Time']." ";// echo "Place: ".$row['PE_Place']." ";// echo "PE_Total_KM: ".$row['PE_Total_KM']." ";// echo "PE_KM_Travelled: ".$row['PE_KM_Travelled']." ";// echo "PE_Litres_Used: ".$row['PE_Litres_Used']." ";// echo "<br />";}HERE IS MY LOGIN CODE<?phprequire_once("header.html");require_once('config.php');if ($_POST['username'] != '' && $_POST['passwd'] != ''){ $user = $_POST[username]; $password = $_POST[passwd]; $sql = "SELECT * from vehicle WHERE VE_Reg_Number = '$user' and VE_Password= '$password'"; $result = mysql_query($sql) or die(mysql_error()); $row = mysql_fetch_assoc($result); $num_rows = mysql_num_rows($result); if (!$num_rows){ $errormessage = "Incorrect username OR password, please try again"; } else{ $_SESSION['veid'] = $user; $_SESSION['id'] = $row[VE_ID]; header("location:ptransactions.php"); }}?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>LOG BOOK TRACKER</title><link href="css" rel="stylesheet" /></head><body><form id="form1" name="form1" method="post" action="test.php"> <table width="200" align="center"> <tr > <td colspan="2"><div align="center"><strong>TRACKING LOGIN</strong></div></td> </tr> <tr align="center" bgcolor="#008DC6"> <td colspan="2" class="header">LOGIN</td> </tr> <tr bgcolor="#EEEEEE"> <td>UserName</td> <td><input type="text" class="text" name="username" id="username" /></td> </tr> <tr bgcolor="#EEEEEE" > <td>PassWord</td> <td><input type="password" class="text" name="password" id="passwd" /></td> </tr> <tr bgcolor="#008DC6" > <td colspan="2" align="center"><input type="submit" name="submit" class="button" id="button" value="Submit" /></td> </tr> <tr> <td colspan="2" class="loginbox" ><span class="orangebold"><?php print $errormessage;?></span></td> </tr> <tr> <td> </td> <td> </td> </tr> </table></form></div><?phprequire_once("footer.html");?></body></html>

Link to comment
Share on other sites
justsomeguy w3schools Guru

justsomeguy

Posted
Posted

What part are you having problems with? It looks like you're already getting the information from the database in the login code. You're saving a vehicle ID or something in the session, can't you use that on the other page to get the other information you're looking for?

Link to comment
Share on other sites
yrstruly Member

yrstruly

Posted
  • Author
Posted
What part are you having problems with? It looks like you're already getting the information from the database in the login code. You're saving a vehicle ID or something in the session, can't you use that on the other page to get the other information you're looking for?
HalloThank you for the reply.Yes right now it is just giving me the output of the vehicle registration number and i need more info/output then that but i dont know how. Hi there.Can you please have a look at:www.triptrack.co.za . As you look at that website(www.tricptrack), i have build a logg in code, that works now(i will paste the login code also in here so that you can see),after the user has put in he's details the login page, that page should go to the tables page, which contains the users info from the database(i will also attached that table code) The trouble is i dont know the neccesary coding(sql) to make that happen. Can you please help me out there? All i have now when i logg in is the vehicle registration number and it should be all of this, neatly in that colorful table,follows:"Date: "."Place: // echo "PE_Total_KM: "PE_KM_Travelled: "PE_Litres_Used: ".Here is the login code<?phprequire_once("header.html");require_once('config.php');if ($_POST['username'] != '' && $_POST['passwd'] != ''){$user = $_POST[username];$password = $_POST[passwd];$sql = "SELECT * from vehicle WHERE VE_Reg_Number = '$user' and VE_Password= '$password'";$result = mysql_query($sql) or die(mysql_error());$row = mysql_fetch_assoc($result);$num_rows = mysql_num_rows($result);if (!$num_rows){$errormessage = "Incorrect username OR password, please try again";}else{$_SESSION['veid'] = $user;$_SESSION['id'] = $row[VE_ID];header("location:ptransactions.php");}}?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>LOG BOOK TRACKER</title><link href="css" rel="stylesheet" /></head><body><form id="form1" name="form1" method="post" action="test.php"><table width="200" align="center"><tr ><td colspan="2"><div align="center"><strong>TRACKING LOGIN</strong></div></td></tr><tr align="center" bgcolor="#008DC6"><td colspan="2" class="header">LOGIN</td></tr><tr bgcolor="#EEEEEE"><td>UserName</td><td><input type="text" class="text" name="username" id="username" /></td></tr><tr bgcolor="#EEEEEE" ><td>PassWord</td><td><input type="password" class="text" name="password" id="passwd" /></td></tr><tr bgcolor="#008DC6" ><td colspan="2" align="center"><input type="submit" name="submit" class="button" id="button" value="Submit" /></td></tr><tr><td colspan="2" class="loginbox" ><span class="orangebold"><?php print $errormessage;?></span></td></tr><tr><td> </td><td> </td></tr></table></form></div><?phprequire_once("footer.html");?></body></html>HERE IS THE SQL CODE that checks the details<?phpsession_start();require_once('config.php');$registration_number = $_REQUEST['username'];$password = $_REQUEST['password'];//Check that password entered is correct and get the clients email address for this vehicle IDsession_start();$enc_password = md5($password);$password_query = "SELECT VE_IDFROM `vehicle` WHERE `VE_Reg_Number` = '$registration_number'AND `VE_Password` = '$enc_password'" ;//echo $password_query;echo "<br>";if(!$password_result = mysql_query($password_query)) {die("Error with the database. Please try again later");}if(mysql_num_rows($password_result)== 0) {die("Password is incorrect. Please try again."); }$row = mysql_fetch_assoc($password_result);$Vehicle_ID = $row['VE_ID'];//echo "Vehicle id is $Vehicle_ID<br>";//Get records for this vehicle_id from the $petrol_query = "SELECT `PE_Place` , `PE_Date_Time` , `PE_Total_KM` , `PE_KM_Travelled` , `PE_Litres_Used` FROM `petrol_entries` WHERE `VE_ID` =$Vehicle_IDORDER BY `petrol_entries`.`PE_Date_Time` ASC ";//echo $petrol_query;echo "<br>";if(!$petrol_result = mysql_query($petrol_query)) {die("Error with the database. Please try again later");}if(mysql_num_rows($petrol_result)== 0) {die("No records exist"); }?>Your vehicle regisration number is <?phpecho "$registration_number";echo "<br />";while($row = mysql_fetch_assoc($petrol_result)) {$number_of_entries ++;// echo "Date: ".$row['PE_Date_Time']." ";// echo "Place: ".$row['PE_Place']." ";// echo "PE_Total_KM: ".$row['PE_Total_KM']." ";// echo "PE_KM_Travelled: ".$row['PE_KM_Travelled']." ";// echo "PE_Litres_Used: ".$row['PE_Litres_Used']." ";// echo "<br />";}Can you please help me out?Tables code <?phprequire_once("header.html");session_start();require_once('config.php');$id=$_SESSION[id];$veid=$_SESSION['veid'];$conn = mysql_connect(localhost,root,musica) or die(mysql_error());mysql_select_db(petrod_db1, $conn) or die(mysql_error());$sql = "SELECT * from petrol_entries WHERE VE_ID = '$id'";$result = mysql_query($sql, $conn) or die(mysql_error());$row = mysql_fetch_assoc($result);$num_rows = mysql_num_rows($result);?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>Untitled Document</title><style type="text/css">.col0 {background-color: #ffaaaa}.col1 {background-color: #55ffff}.col2 {background-color: #aaffaa}.col3 {background-color: #aaaaff}.col4 {background-color: #ff55ff}</style></head><body><table width="100%" align="center"><tr><td colspan="2"><div align="center">PETROL TRANSACTIONS</div></td></tr><tr><td height="29" nowrap="nowrap"><div align="right">Vihecle Reg</div></td><td nowrap="nowrap">Test</td></tr><tr><td><div align="right">Make</div></td><td><input type="text" name="make" id="make" /></td></tr><tr><td> </td><td> </td></tr></table><table width="100%" align="center"><col class="col0" /><col class="col1" /><col class="col2" /><col class="col3" /><col class="col4" /><tr><td>Date</td><td>Place</td><td>Travelled</td><td>KMS</td><td>litre</td></tr><tr><td>Cell 1</td><td>Cell 2</td><td>Cell 3</td><td>Cell 4</td><td>Cell 5</td></tr><tr><td>Cell 1</td><td>Cell 2</td><td>Cell 3</td><td>Cell 4</td><td>Cell 5</td></tr><tr><td>Cell 1</td><td>Cell 2</td><td>Cell 3</td><td>Cell 4</td><td>Cell 5</td></tr><tr><td>Cell 1</td><td>Cell 2</td><td>Cell 3</td><td>Cell 4</td><td>Cell 5</td></tr><tr><td>Cell 1</td><td>Cell 2</td><td>Cell 3</td><td>Cell 4</td><td>Cell 5</td></tr><tr><td>Cell 1</td><td>Cell 2</td><td>Cell 3</td><td>Cell 4</td><td>Cell 5</td></tr><tr><td>Cell 1</td><td>Cell 2</td><td>Cell 3</td><td>Cell 4</td><td>Cell 5</td></tr><tr><td>Cell 1</td><td>Cell 2</td><td>Cell 3</td><td>Cell 4</td><td>Cell 5</td></tr><tr><td>Cell 1</td><td>Cell 2</td><td>Cell 3</td><td>Cell 4</td><td>Cell 5</td></tr><tr><td>Cell 1</td><td>Cell 2</td><td>Cell 3</td><td>Cell 4</td><td>Cell 5</td></tr><tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr></table><p> </p></body></html>
Link to comment
Share on other sites
justsomeguy w3schools Guru

justsomeguy

Posted
Posted

It looks like you're already getting everything here:$sql = "SELECT * from petrol_entries WHERE VE_ID = '$id'";$result = mysql_query($sql, $conn) or die(mysql_error());You just loop through the result set and show whatever you want to show.

while ($row = mysql_fetch_assoc($result)){  echo $row['VE_ID'];  ...}

Link to comment
Share on other sites
yrstruly Member

yrstruly

Posted
  • Author
Posted
It looks like you're already getting everything here:$sql = "SELECT * from petrol_entries WHERE VE_ID = '$id'";$result = mysql_query($sql, $conn) or die(mysql_error());You just loop through the result set and show whatever you want to show.
while ($row = mysql_fetch_assoc($result)){  echo $row['VE_ID'];  ...}

Hi thak you for the reply. I know yr doing your best to help me and i truly appreciate it, but im all new to this and i thought this forum was to help people like me. Everybody talks to me like im a pro, i aint one and i have no idea what you just said, so it means nothing to me. Can you please eloborate and maybe shoe me a full example?Pls.
Link to comment
Share on other sites
justsomeguy w3schools Guru

justsomeguy

Posted
Posted

These two lines get the records from the database and store them in the variable $result:$sql = "SELECT * from petrol_entries WHERE VE_ID = '$id'";$result = mysql_query($sql, $conn) or die(mysql_error());In order to loop through the results and show each one, it's common to use a structure like this:

while ($row = mysql_fetch_assoc($result)){  echo $row['VE_ID'];}

All that will do is print out the VE_ID for each record and nothing else. You can expand on that to print your table structure also, but if you're new at this then just start with the database work. Once you understand how to get records from a database and read them, then you can set up your table. I also noticed that you aren't quoting any strings, if you have a string value it needs to be quoted. If you leave the quotes off then PHP thinks you're referring to a constant. So the string values in mysql_connect and mysql_select_db need quotes around them. So set up a simple page that connects to the database, gets the records, and loops through them. Once you understand how it works then you can expand on it to show the info however you want.

<?php$conn = mysql_connect('localhost','root','musica') or die(mysql_error());mysql_select_db('petrod_db1', $conn) or die(mysql_error());$sql = "SELECT * from petrol_entries WHERE VE_ID = '$id'";$result = mysql_query($sql, $conn) or die(mysql_error());echo '<pre>';echo "There were " . mysql_num_rows($result) . " records found.\n\n";$nr = 0;while ($row = mysql_fetch_assoc($result)){  $nr++;  echo "Row {$nr} (VE_ID: {$row['VE_ID']}):\n";  print_r($row);}echo '</pre>';?>

That script will get everything from the database, show how many records were found, and for each record it will print the row number, the ID, and then output the entire row array so you can see everything in the row. Look at how that works and mess around with it to get it to show what other information you want. All of the values for each record are in the $row array, you can print any of them. Instead of having <pre> tags around the while loop, you can replace that with your <table> structure and have the inside of the loop print a <tr> with the info in the different <td> elements.Also, if you haven't worked with PHP you're going to want to go through the PHP tutorials and learn about the language in general. Copying and pasting code isn't going to help you learn anything if you don't take the time to read about the theory (and no one here has the time to teach everyone who stops by all the theory they need individually, but I have some sticky topics in the PHP forum that have some tips in them).

Link to comment
Share on other sites
yrstruly Member

yrstruly

Posted
  • Author
Posted

HalloThank you so much for your help so far, i really appreciate it.I need some more help. I have progresed with this webite im developing till the stage where i can get info out o the database and now i need an sql/php code that will give me an option to go back to the previous page, after i have updated and saved the latest info. Can you help please. Here follows the code i have.<?php require_once('config.php'); $id=$_REQUEST['id']; $vehicle=$_REQUEST['vehicle']; $date1=$_REQUEST['date1']; $place=$_REQUEST['place']; $tkm=$_REQUEST['tkm']; $travelled=$_REQUEST['travelled']; $filled=$_REQUEST['filled']; $cost=$_REQUEST['cost']; $consumption=$_REQUEST['consumption']; $conn = mysql_connect(localhost,root,musica) or die(mysql_error()); $sql = "UPDATE petrol_entries SET PE_Total_KM = '$tkm' WHERE PE_ID = '$id'"; $result = mysql_query($sql, $conn) or die(mysql_error());?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>Save</title></head><body></body></html>Here is my test code<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>Petrol Logs</title><?phprequire_once('config.php');$registration_number = $_REQUEST['username'];$password = $_REQUEST['password'];//Check that password entered is correct and get the clients email address for this vehicle ID$enc_password = md5($password);$password_query = "SELECT VE_IDFROM `vehicle` WHERE `VE_Reg_Number` = '$registration_number'AND `VE_Password` = '$enc_password'";//echo $password_query;echo "<br>";if(!$password_result = mysql_query($password_query)) { die("Error with the database. Please try again later");}if(mysql_num_rows($password_result)== 0) { die("Password is incorrect. Please try again."); }$row = mysql_fetch_assoc($password_result);$Vehicle_ID = $row['VE_ID'];//echo "Vehicle id is $Vehicle_ID<br>";//Get records for this vehicle_id from the $petrol_query = "SELECT `PE_Place` , `PE_Date_Time` , `PE_Total_KM` , `PE_KM_Travelled` , `PE_Litres_Used` FROM `petrol_entries` WHERE `VE_ID` =$Vehicle_IDORDER BY `petrol_entries`.`PE_Date_Time` ASC ";//echo $petrol_query;echo "<br>";if(!$petrol_result = mysql_query($petrol_query)) { die("Error with the database. Please try again later");}if(mysql_num_rows($petrol_result)== 0) { die("No records exist"); }while($row = mysql_fetch_assoc($petrol_result)) { $number_of_entries ++;// echo "Date: ".$row['PE_Date_Time']." ";// echo "Place: ".$row['PE_Place']." ";// echo "PE_Total_KM: ".$row['PE_Total_KM']." ";// echo "PE_KM_Travelled: ".$row['PE_KM_Travelled']." ";// echo "PE_Litres_Used: ".$row['PE_Litres_Used']." ";// echo "<br />";}require_once("header.html");$id=$_SESSION[id];$veid=$_SESSION['veid'];$conn = mysql_connect(localhost,root,musica) or die(mysql_error());mysql_select_db(petrod_db1, $conn) or die(mysql_error());$sql = "SELECT * from petrol_entries WHERE VE_ID = '$id'";$result = mysql_query($sql, $conn) or die(mysql_error());$row = mysql_fetch_assoc($result);$num_rows = mysql_num_rows($result);?><style type="text/css">.col0 {background-color: #ffaaaa}.col1 {background-color: #55ffff}.col2 {background-color: #aaffaa}.col3 {background-color: #aaaaff}.col4 {background-color: #ff55ff}</style></head><body><table width="100%" align="center"><tr><td colspan="2"><div align="center">PETROL TRANSACTIONS</div></td></tr><tr><td height="29" nowrap="nowrap"><div align="right">Vehicle Reg</div></td><td nowrap="nowrap"><?php echo "$registration_number"; ?></td></tr><tr><td><div align="right">Make</div></td><td><input type="text" name="make" id="make" /></td></tr><tr><td> </td><td> </td></tr></table><table width="100%" align="center"><col class="col0" /><col class="col1" /><col class="col2" /><col class="col3" /><col class="col4" /><tr><td>Date</td><td>Place</td><td>Travelled</td><td>KMS</td><td>litre</td></tr><?php $query = "SELECT PE_DATE_TIME, PE_Place, PE_KM_Travelled, PE_Total_KM, PE_Litres_Used, PE_ID from petrol_entries WHERE VE_ID = '$Vehicle_ID'"; $result = mysql_query($query) or die("Could not perform query: " . mysql_error()); while ($line = mysql_fetch_array($result)) { echo "<a href=transactions.php?id=$line[5] >"; echo "<tr>"; echo "<td>"; echo "$line[0]"; echo "</td>"; echo "<td>"; echo "$line[1]"; echo "</td>"; echo "<td>"; echo "$line[2]"; echo "</td>"; echo "<td>"; echo "$line[3]"; echo "</td>"; echo "<td>"; echo "$line[4]"; echo "</td>"; echo "</tr>"; echo "</a>"; }?></table><p> </p></body></html>Thank you.

These two lines get the records from the database and store them in the variable $result:$sql = "SELECT * from petrol_entries WHERE VE_ID = '$id'";$result = mysql_query($sql, $conn) or die(mysql_error());In order to loop through the results and show each one, it's common to use a structure like this:
while ($row = mysql_fetch_assoc($result)){  echo $row['VE_ID'];}

All that will do is print out the VE_ID for each record and nothing else. You can expand on that to print your table structure also, but if you're new at this then just start with the database work. Once you understand how to get records from a database and read them, then you can set up your table. I also noticed that you aren't quoting any strings, if you have a string value it needs to be quoted. If you leave the quotes off then PHP thinks you're referring to a constant. So the string values in mysql_connect and mysql_select_db need quotes around them. So set up a simple page that connects to the database, gets the records, and loops through them. Once you understand how it works then you can expand on it to show the info however you want.

<?php$conn = mysql_connect('localhost','root','musica') or die(mysql_error());mysql_select_db('petrod_db1', $conn) or die(mysql_error());$sql = "SELECT * from petrol_entries WHERE VE_ID = '$id'";$result = mysql_query($sql, $conn) or die(mysql_error());echo '<pre>';echo "There were " . mysql_num_rows($result) . " records found.\n\n";$nr = 0;while ($row = mysql_fetch_assoc($result)){  $nr++;  echo "Row {$nr} (VE_ID: {$row['VE_ID']}):\n";  print_r($row);}echo '</pre>';?>

That script will get everything from the database, show how many records were found, and for each record it will print the row number, the ID, and then output the entire row array so you can see everything in the row. Look at how that works and mess around with it to get it to show what other information you want. All of the values for each record are in the $row array, you can print any of them. Instead of having <pre> tags around the while loop, you can replace that with your <table> structure and have the inside of the loop print a <tr> with the info in the different <td> elements.Also, if you haven't worked with PHP you're going to want to go through the PHP tutorials and learn about the language in general. Copying and pasting code isn't going to help you learn anything if you don't take the time to read about the theory (and no one here has the time to teach everyone who stops by all the theory they need individually, but I have some sticky topics in the PHP forum that have some tips in them).

Link to comment
Share on other sites
justsomeguy w3schools Guru

justsomeguy

Posted
Posted

The previous page will be in the PHP variable $_SERVER['HTTP_REFERER']. You can use that to either redirect there or print a link they can click on. Keep in mind that once that happens the $_REQUEST and $_POST variables will be empty. If you want to re-use that information you need to save it in the session before redirecting.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...