Relitively new to all this!

irishmaggie

Hi there,
I have a bit of a problem and I was told that you are the guys to come to!...so here goes!

I am building a small web site for my thesis and I am running into a few small problems! I am allowing user to surf my site and book a mini-bus journey and at the end I want to issue them a unique reference number, but I already have an autonumber in my journey table and I cannot use two. I cannot change this as it is a primary key.

Here is my table structure, (by the way I am using MySQL):

create table journey
(journey_id int not null primary key auto_increment,
journey_date date not null,
customer_id int not null,
commence_location varchar(55) not null,
end_location varchar(55) not null,
number_passengers enum('10 - 15', '15 - 19', '19 - 21', '21 - 25', '25 - 30', '30 - 47') not null,
journey_duration enum('Half a Day/Night', 'Weekend', 'Full Day', '2 Days', '3 Days', '4 Days', 'Full Week', 'Two Weeks') not null,
pickuptime time not null,
pickuplocation varchar(101) not null,
price_id int not null,
reference_id int not null unique);

How can I allow them to click continue and bring them to a page with the ref. number on it and also have that stored in my reference_id field in my journey table???

If you could help me that would be excellent...I have tried everything!
Regards,
irishmaggie!*

barand

A second table , say "bookings" and create a record with journey id, passenger name and their unique bookingId (autonumber field on this table). You could then issue them with a number of bookingId.

For each journey you would also have a list of booked passengers as a byproduct.

irishmaggie

I have to have my thesis in today, thank you for your help though, I just thought it would be something simple...my supervisor made me change a load of things last Thursday!
Thank you for all your help though, I am not ungrateful!
If I go inserting another table too much has to be changed but if it's the only way to do it thats okay!

Here's another problem though!

I keep getting this message:

  Duplicate entry '0' for key 2

I am trying to match id's in order to create a link between my two tables by matching the name in one table with the name the user enters,
Any Suggestions?

Here's my php code:

<?php
//connect to MySQL
$conn = @mysql_connect("localhost", "mags", "mags101")
or die("Could not connect to MySQL");

//select the specified database
$rs = @mysql_select_db("allensbushire", $conn)
or die("Could not select database");

#compare names and get the id that matches that name!
$sql="select customer_id from customer where customer_name='$HTTP_POST_VARS[requiredjname]'";

$rs1=mysql_query($sql)or die (mysql_error());

#get number of rows that match query
$num = mysql_numrows($rs1);

#if there is a match the data from the form is entered!
if($num != 0)
{
$row= mysql_fetch_array($rs1);
$sql = "INSERT INTO journey (journey_date, customer_id, commence_location, end_location, number_passengers, journey_duration, pickuptime, pickuplocation) VALUES ('$HTTP_POST_VARS[requiredjdate]', '$row[0]', '$HTTP_POST_VARS[requiredcommloc]', '10 Miles', '$HTTP_POST_VARS[requirednopass]', '$HTTP_POST_VARS[requiredjdur]', '$HTTP_POST_VARS[requiredpickuptime]', '$HTTP_POST_VARS[requiredpickuploc]')";

$result = mysql_query($sql)or die (mysql_error());

#$rs2=mysql_query($sql, $conn);

#header("Location: CreditCard Page.htm");

}
else
{
echo("INCORRECT!");
exit();
}

Any help would be greatly appriciated!
Cheers!

barand

Is it because you haven't yet picked up the customer id from your query?

irishmaggie

Sorry,
I am not quite sure what you mean?
My fields are well populated in my table and I am running the query before I go into the if statement, so I cannot see the problem.

I thought that it might be that it is actually outting in the array element number and not the number stored in the array, if you know what I mean! But I don't know how to rectify it???
Regards,
irishmaggie!*

barand

Does $row['customer_id'] instead of $row[0] in the insert query improve matters?

irishmaggie

Nope!
It gave me this error:
Parse error: parse error, expecting T_STRING' orT_VARIABLE' or `T_NUM_STRING' in c:\phpweb\abhweb site\custjourneyrecord.php on line 21

...and when I tried this:

       '$row[customer_id]'

......it gave me the same old:

       Duplicate entry '0' for key 2

By the way, if I clear the table and run it, it actually adds a record but not the correct customer_id in relation to the name!

I totally so give up, how do ye do it all day!
If you have any more suggestions throw them this way,
Regards,
irishmaggie!*

barand

not

$row[customer_id]

but

$row['customer_id'] // quotes required

irishmaggie

Yes, I tried that the last time, that's what gave me the parse error!

I also tried variations of this and to no avail!

Regards,
irishmaggie!*

barand

$row= mysql_fetch_array($rs1); 

$cust = $row["customer_id"]; //add this line , use $cust in query


$sql = "INSERT INTO journey (journey_date, customer_id, commence_location, end_location, number_passengers, journey_duration, pickuptime, pickuplocation) VALUES ('$HTTP_POST_VARS[requiredjdate]', '$cust', '$HTTP_POST_VARS[requiredcommloc]', '10 Miles', '$HTTP_POST_VARS[requirednopass]', '$HTTP_POST_VARS[requiredjdur]', '$HTTP_POST_VARS[requiredpickuptime]', '$HTTP_POST_VARS[requiredpickuploc]')";

irishmaggie

You are a Star!!!!!!!!
I was so close to that, except I was using the:
$row= mysql_fetch_array($rs1, MYSQL_ASSOC)
it must have been the MYSQL_ASSOC that was preventing it from working properly.
I also had to take out my primary and unique keys...would I have to leave them out now or can they be put back in do you think.............oh I don't care! I finally have it!!!!!
Thanks a million Barand!
Regards,
irishmaggie!*