Running Queries ( Hey guys please help, Uni student doing his re-sits)

thryb · August 22, 2007

'$Customer Username'

there is your probleme in your insert... should be

'$CustomerUsername'

adamdyer · August 22, 2007

hmm! just tried that and its still the same :-S

MadTechie · August 22, 2007

change to

$sql = "INSERT INTO `Booking Database` (`Customer Username`, `Booking ID`, `Check In`, `Check Out`, `Price Cat`, `Type Of Room`, `Password`) VALUES ( '$CustomerUsername', '$BookingID', '$CheckIn', '$CheckOut', '$PriceCat', '$TypeOfRoom', '$Password' )";

can you post the form and the $_GET['CustomerUsername'] part if this fails

MadTechie · August 22, 2007

Opps last code was wrong..!

use

$sql = "INSERT INTO `Booking Database` (`Customer Username`, `Booking ID`, `Customer Name`, `Check In`, `Check Out`, `Price Cat`, `Type Of Room`, `Password`) VALUES ( '$CustomerUsername', '$BookingID', '$CustomerName', '$CheckIn', '$CheckOut', '$PriceCat', '$TypeOfRoom', '$Password' )";

Still need to see the

$CustomerUsername = $_GET[

and the CustomerUsername part of the form

adamdyer · August 22, 2007

yay! that bit worked :-D (thank F*^K for that)

ive still got lots of other things im stuck on though, do you think you'll still be able to help me? its cool if you cant though :-) youve done enough already.

MadTechie · August 22, 2007

Sure:)

adamdyer · August 22, 2007

cool, thanks :-)

Ok, so next part of my problem is such - I have to be able to retreive the data from my Database so that the customers are sorted into two seperate fields of £40 and £60.

PHP Coding

<html>
<head>
<title>View Customers by category</title>
</head>
<body>

<?php 

#get the Customer Price Category i.e. Acat variable
$Cat = $_GET['Acat'];

#--------------------------------------------------------

echo("<font face=Courier size=3>");
echo("<p>Customer Price Category Results for :$CAT <hr></P>");
$VariableName = $_GET['VariablePassed'];
#--------------------------------------------------------

# connect to MySQL
$conn = @mysql_connect("localhost","ajdref","ref2")
or die ("sorry - could not wont connect to MySQL"); 

# select the specified database
$rs = @mysql_select_db("ajdref", $conn)
or die ("sorry - could not connect to the ajdref");

#create the query - sets $sql to contain the required query
$sql = 'SELECT * FROM `Booking Database` ORDER BY `Booking Database` . `Price Cat` ASC LIMIT 0, 30 '; 

#execute the query - uses the mysql_query() function to issue the $sql query to the $conn connection
#$rs contains the result set in the form of an array
$rs = mysql_query($sql,$conn);

?>



</body>
</html>

HTML Coding

<HTML>
<HEAD>
<TITLE> Search by category </TITLE>
</HEAD>
<BODY BGCOLOR=lemonchiffon >

<form NAME="GetCode" ACTION="customerpricecategory2.php" METHOD=get>

  <div align="center"><b><font size="4"> Search by Price Category</font></b></div>
      
  <p></p>
                    
  <div align="center">
    <p>Please choose price category from this list. 
      <select name="Acat">
          <option value="A" selected>A - £40 </option>
          <option value="B">B - £60 </option>
      </select>
    </p>  
  </div>
  
  <p></p>
                  
  <div align="center">Now click 'Send'. 
    <input type="submit" value="Send" name="SUBMIT" >
  </div>
</form>
</BODY>
</HTML>

MadTechie · August 23, 2007

OK from here, we're use the WHERE clause

<?php
//$VariableName = $_GET['VariablePassed'];
$Acat = $_GET['Acat ']; //Acat  being the form element name were passing
#--------------------------------------------------------

//...snip...//

#create the query - sets $sql to contain the required query
//$sql = 'SELECT * FROM `Booking Database` ORDER BY `Booking Database` . `Price Cat` ASC LIMIT 0, 30';
$sql = 'SELECT * FROM `Booking Database` ORDER BY `Price Cat` WHERE  `Price Cat` = $Acat LIMIT 0, 30';
?>

adamdyer · August 23, 2007

do you mean to replace the red SQL with the yellow SQL?

MadTechie · August 23, 2007

i have commented out the lines

//$VariableName = $_GET['VariablePassed'];
//$sql = 'SELECT * FROM `Booking Database` ORDER BY `Booking Database` . `Price Cat` ASC LIMIT 0, 30';

and replaced them with

$Acat = $_GET['Acat ']; //Acat  being the form element name were passing
$sql = 'SELECT * FROM `Booking Database` ORDER BY `Price Cat` WHERE  `Price Cat` = $Acat LIMIT 0, 30';

Basically the 'Acat' is the element in the form (thus the new GET) and

i am selecting the records where `Price Cat` = $Acat (field `Price Cat` = the value of the Acat field)

adamdyer · August 23, 2007

ok done that - so my coding now looks like

<html>
<head>
<title>View Customers by category</title>
</head>
<body>

<?php 

#get the Customer Price Category i.e. Acat variable
$Cat = $_GET['Acat'];

#--------------------------------------------------------

echo("<font face=Courier size=3>");
echo("<p>Customer Price Category Results for :$CAT <hr></P>");
$Acat = $_GET['Acat ']; //Acat  being the form element name were passing
#--------------------------------------------------------

# connect to MySQL
$conn = @mysql_connect("localhost","ajdref","ref2")
or die ("sorry - could not wont connect to MySQL"); 

# select the specified database
$rs = @mysql_select_db("ajdref", $conn)
or die ("sorry - could not connect to the ajdref");

#create the query - sets $sql to contain the required query
$sql = 'SELECT * FROM `Booking Database` ORDER BY `Price Cat` WHERE  `Price Cat` = $Acat LIMIT 0, 30';

#execute the query - uses the mysql_query() function to issue the $sql query to the $conn connection
#$rs contains the result set in the form of an array
$rs = mysql_query($sql,$conn);

?>




</body>
</html>

But I still get this message:

Customer Price Category Results for :

(Then a Horizontal Line)

Then Blank.

MadTechie · August 23, 2007

oh

$rs = mysql_query($sql,$conn);

//add code below
while ($row = mysql_fetch_assoc($rs))
{
    echo $row["Customer Username"];
    echo $row["Booking ID"];
    echo $row["Check In"];
}

adamdyer · August 23, 2007

hmm! i seemed to have mesed up somewher - im gonna start those pages again, if i get any probs I'll repost here

MadTechie · August 23, 2007

change

$rs = mysql_query($sql,$conn);

to

$rs = mysql_query($sql,$conn)or die(mysql_error());

it may reveal the problem

adamdyer · August 23, 2007

ok - i re-did the page and now it look like this

<!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=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<html>
<head>
<title>View Customers by price category</title>
</head>
<body>

<?php 

#get the Car category i.e. Acat variable
$CAT = $_GET['Acat'];

#--------------------------------------------------------

echo("<font face=Courier size=3>");
echo("<p>Car Results for :$CAT <hr></P>");

#--------------------------------------------------------

?>

<?php

# connect to MySQL
$conn = @mysql_connect("localhost","ajdref","ref2")
or die ("sorry - could not connect to MySQL"); 

# select the specified database
$rs = @mysql_select_db("ajdref", $conn)
or die ("sorry - could not connect to the database");

#create the query - sets $sql to contain the required query
SELECT * FROM Booking Database where price cat = '$CAT'

#execute the query - uses the mysql_query() function to issue the $sql query to the $conn connection
#$rs contains the result set in the form of an array
$rs = mysql_query($sql,$conn)or die(mysql_error());

?>

</body>
</html>

but after re-doing them its gone back to being a complete blank page :-(

MadTechie · August 23, 2007

SELECT * FROM Booking Database where price cat = '$CAT'

should be

$sql = "SELECT * FROM Booking Database where price cat = '$CAT'";

also you need to add this

oh

$rs = mysql_query($sql,$conn);

//add code below
while ($row = mysql_fetch_assoc($rs))
{
    echo $row["Customer Username"];
    echo $row["Booking ID"];
    echo $row["Check In"];
}

adamdyer · August 23, 2007

done - and it changed , we're getting somewhere :-D

now i get this

customerpricecategory3.php for :A

-------------------------------------------------------------------------------

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 'Database where price cat = 'A'' at line 1

MadTechie · August 23, 2007

i should of checked this

$sql = "SELECT * FROM Booking Database where price cat = '$CAT'";

to

$sql = "SELECT * FROM `Booking Database` where `price cat` = '$CAT'";

i assume price cat contains A's & B's ?

adamdyer · August 23, 2007

yeh categories A and B

A- £40

B - £60

Changed what you said, am getting the right results now, just in a 'funny way'

customerpricecategory3.php for :A

--------------------------------------------------------------------------------

000001000001A1st Jan 2007000004000004A6th August 2007000005000005A14th Sept 2007000006000006A5th July 2007adamdyer12349th April

Sign In

Running Queries ( Hey guys please help, Uni student doing his re-sits)

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information