Sub queries

[scoobie]

Hi,

i was wondering if anyone can help me. i have an insert query that puts data into a ticket table. but before i insert the data i want to get an id number from a different table based on a choice that was made in the form.

Code

/* SELECT CHRGID FROM CHRGTBL */
$qry = "select ChrgID from chrgtbl where chrgtbl.TimeData = '$TimeOptFrm'";
$result = mysql_query($qry) or die(mysql_error());

/* INSERT DETAILS INTO TICKETTBL */
$insert = "insert into tickettbl (AccID, ChrgID, TicketNo, TimeData, RecDate)
values (LAST_INSERT_ID(), ChrgID, '$Random', '$TimeOptFrm', '$TodaysDate')";

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

if ($result)
echo "Your ticket is stored";

Can anybody help, i know it probably is something simple but i can't seem to get it working
Thanks in Advanced,

Scoobie.

[dreamcatcher]

Hi scoobie,

Change:

$qry = "select ChrgID from chrgtbl where chrgtbl.TimeData = '$TimeOptFrm'";
$result = mysql_query($qry) or die(mysql_error());

to:

$qry = "select ChrgID from chrgtbl where chrgtbl.TimeData = '$TimeOptFrm'";
$result = mysql_query($qry) or die(mysql_error());
$row = mysql_fetch_object($result);

Then Change:

$insert = "insert into tickettbl (AccID, ChrgID, TicketNo, TimeData, RecDate)
values (LAST_INSERT_ID(), ChrgID, '$Random', '$TimeOptFrm', '$TodaysDate')";

to:

$insert = "insert into tickettbl (AccID, ChrgID, TicketNo, TimeData, RecDate)
values (LAST_INSERT_ID(), '$row->ChrgID', '$Random', '$TimeOptFrm', '$TodaysDate')";

That should work ok. I have used mysql_fetch_object to get your data from the first query, but you can use any of mysql`s fetch syntaxes.

dc

[scoobie]

thanks very much DC, i got that to work.

i was wondering if you could help me another problem,

in the same insert statement i use Last_Insert_ID() which will insert the AccID in the ticket table when the person registers for the first time. however, if the person is already registered and logs in i need to also store their AccID. i tried using session but had no idea how to do it

Any Ideas?

Scoobie

[dreamcatcher]

scoobie,

Can you not query the database when they log in and then pull the id number from the database? If nothing returns (use mysql_num_rows), then assume they are not registered.

$query = mysql_query("SELECT id FROM table WHERE user = '$username' LIMIT 1") or die(mysql_error());
$row = mysql_fetch_object($query);

if (mysql_num_rows($query)==0)
{
//not registered
}
else
{
echo "ID No: " . $row->id;
}

If you want their id to be available to you as soon as they access your page, then you are going to need to store their id number in a cookie when they register. Or something else unique to that particular user.

Or am I not understanding you correctly?

[scoobie]

you understand fine, that is what i want to do. the problem is i don't know how to set up a cookie and store the AccID from the members table that is related to the username and password that the user logs in with i.e. if their Username = abc & password = def then the accID stored in cookie is AccID = 17.

i don't now how to carry it over the different pages. i have tried setting it up but got nothing working.

if you could give me any help that would be great.

thanks,

scoobie

[dreamcatcher]

Ok, well lets assumed someone just registered. Set a cookie when they register with their username (note I`m assuming POST data here)

@setcookie("username", $_POST['username']); //You can set an expiry time if you like
@setcookie("username", $_POST['username'], time()+60*60*24*180);); //this is for 180 days

Then when they come back to your site, check the cookie like this:

if (isset($_COOKIE['username']))
{
echo "Welcome back " . $_COOKIE['username'];

$query = mysql_query("SELECT id FROM table WHERE username = '" . $_COOKIE['username'] . "' LIMIT 1") or die(mysql_error());
$row = mysql_fetch_object($query);

//You can now do what you want with the $row->id variable.

}
else
{
echo "This is your first visit. Hi there!";
}

Does that help any?

[scoobie]

Hi,

i tried your code but its not storing anything in the cookie.

i put:

@setcookie("username", $_POST['username']); //You can set an expiry time if you like
@setcookie("username", $_POST['username'], time()+60*60*24*180);); //this is for 180 days

in the login.php script which runs after they click login on the home page

i put:

if (isset($_COOKIE['username']))
{
echo "Welcome back " . $_COOKIE['username'];
$query = mysql_query("SELECT id FROM table WHERE username = '" . $_COOKIE['username'] . "' LIMIT 1") or die(mysql_error());
$row = mysql_fetch_object($query);
//You can now do what you want with the $row->id variable.
}
else
{
echo "This is your first visit. Hi there!";
}

in the ticket.php script which is the one that writes into the database but i stll get a zero in the AccID Field in the table.

i changed username to be my UNameFrm variable.

is that right?

Scoobie

[dreamcatcher]

I think its probably best if you can give me a snippet of code that posts your form data for registering. Send me a sticky if its too long.

[mcibor]

setcookie must be first in php. Before any includes, before anything is written as html.

I recommend turning all errors. THen you will have a warning (headers already sent).

If not, then check if cookies are enablen on your browser.

best regards
Michal Cibor