[RESOLVED] how can i "tell the future"?

cisplatin

Hi Everyone,

First post :o so take it easy on me 😉

Don't all shout at once but i've not up to speed with hand coding yet as i've opted for a few Dreamweaver extensions to help me build what i needed (plus quite a few manual tweaks).

I've now been asked to add a sort of basic tracking system.....i've included a piece of code that submits a few details to a table when certain pages are accessed, a couple of session ones an timestamp and the page the person is viewing. This all works fine HOWEVER, when it comes to tracking the insert a new item page i would like to know details of the where in it's table this information is going to go.

initially i'd be happy to just get the auto incremented next id as i'd be able to get the true information i'm after when i call the tracking information back 😕 make sense?

Is this possible?? Can anyone point me in the direction of any code that may help me in my quest??

Many thanks,

bpat1434

WHAT?!?! A NEW USER?!?!? Welcome!

After you insert the information with the query, you can call [man]mysql_insert_id/man which will give you the last auto-increment ID generated (i.e. so the row you just inserted).

Alternatively, you can run a quick query to get the value:

SELECT MAX(id) max FROM `table`

Does that answer your question?

cisplatin

Brilliant, Thanks for that bpat1434.....and thanks for the welcome, it looks as though i may become a regular visitor 😃

The current code i'm using, which i think is probably quite basic is:

<?php
// Start Tracker
$q
='insert into tracker (id_user, name_user, page_requested, link_added, date) values
  ("'.addslashes($_SESSION['kt_login_id']).'", "'.addslashes($_SESSION['kt_login_user']).'", "'.addslashes($_SERVER['PHP_SELF']).'", XXXXXXXXXXX, now())';
mysql_query($q) or print(mysql_error());
// End Tracker
?>

This will obviously try to input the information as soon as the page loads, which is fine for all the other pages i'm using it on. Which of your solutions would fit this best.....obviously the SELECT MAX sounds the likely answer and maybe add 1 so it knows the next entry. Hmmm, this will not be very good for multiple users I'm guessing.

Is there therefore a way to 'easily' adapt this to do an operation like this at the same time/just after the main entry has been entered?....think i'm talking myself into my own solution......maybe not :quiet:

Where the XXXXXXX's are i'd ideally like to add the the value that is going to be posted (which will be $link_url)

If anyone has the time to look i can post more of the code 😃

Thanks,

bpat1434

after you say: // End tracker
just do a call to get the mysql last insert id:

$last_id =mysql_insert_id();

That will give you the last inserted id created by mySQL. Shouldn't have to worry about multiple users using that.

cisplatin

wow, thanks for that fast response 😃

:o now i'm getting more confused. Probably down to me not posting enough information and not being too technical.

what i'm trying to do with this page is add info to a one table and add the tracker information to another table.....obviously the trouble for me is that the value i'd 'ideally' like to pass on to my tracker table would be one that is yet to be added to the other table...$link_url (failing that, as i mentioned before, the incremented id from the other table so i could then add this to the tracker table.

would it be possible to post to two tables at the same time? I think this may actually be easier than trying to do it how i am at the minute? i.e. post to one table, retrieve information and then post to another all in one action....or is this how it's done?

So sorry if this all sounds a bit 'stupid', thanks for your patience and understanding 🙂

justsomeone

You can only insert into one table at a time.

So, as has been suggested, use an autoincrement field on the first table, insert the record into that table, then use the mysql_insert_id function to get the value of the autoincremented field in the record which you have just created. You can then insert/update your second table using that value.

As bpat suggests, using a select max instead of mysql_insert_id will cause problems when two different users attempt to add a record at the same time.

cisplatin

Thanks for that justsomeone,

sounds as though i should be able to manage that.

No doubt i'll be back if i fail 😃

cisplatin

Brilliant. Got it sorted!!!!

Many thanks for all your help guys. Didn't even realise it could be done by posting info to one table, calling back the id of it and then posting to another table 😃

Trouble is.....i'm now having a brain meltdown on how the hell i can use this id value i've submitted to the second table to cross refer it to the first table so that i can display a column value for it in my listings page 🙁

Somebody has mentioned mysql_fetch_assoc so i giess i'd best look into that - sounds a bit more than newbie stuff to me. 😉

justsomeone

you need to list both tables in your select statement

select a.something, b.somethingelse
from
onetable a,
anothertable b
where
a.id = b.a_id
;

or something like that - your table and field names will vary 🙂

Read up on SELECT and JOIN

justsomeone

oh, well done on getting this far, by the way.

cisplatin

sorry, been away for a few days.

:bemused: I'm still having trouble with this, sorry.

I've tried the following code in my repeated row to display my results;

<?php
$myResult = mysql_query("SELECT links.link_url,  tracker WHERE links.id=tracker.link_added");
echo $myResult;
?>

and

<?php
$myResult = mysql_query("SELECT links.link_url, FROM links, tracker WHERE links.id=tracker.link_added");
echo $myResult;
?>

but nothing happens (at least i don't get an error 🙂 )

links.id and tracker.link_added are the same by the way. Hmmmm, would it matter if any of these values were empty or would it just skip it for the row in question?

bpat1434

okay, you really need to watch your SQL syntax. There are errors. With your first example, you don't specify FROM where to get this information from. So the query doesn't execute (hence no rows returned!!)

The second one, you have a stray "," after the links.link_url select statement which shouldn't be there. MySQL will be looking for a column name, not the FROM clause, so there's an error there too.

The reason you don't see any errors is because you don't have "or die(mysql_error())" anywhere in there. Any time you do a query call, you need to have that "or die(mysql_error())" call in order to see errors created by mySQL. Otherwise, you won't get anything and will be baffeled (like now).

jmosterb

As justsomeone said above, read up on SELECT and JOIN. Depending on what kind of JOIN you use, you will get different query results depending on which (if any) of links.id or tracker.link_added is empty.

btw, you need to remove the , (comma) after "links.link_url," in your second example code

<?php
$myResult = mysql_query("SELECT links.link_url, FROM links, tracker WHERE links.id=tracker.link_added");
echo $myResult;
?>

Edit: Slow...

cisplatin

Great, thanks for being so patient you guys.

I guess the JOIN bit comes in now...I've now got as far as;

<?php
$query_myResult = "SELECT links.link_url FROM links, tracker WHERE links.id=tracker.link_added";
$myResult= mysql_query($query_myResult, $connLinks) or die(mysql_error());
echo $myResult;
?>

this now returns "Resource id #63" etc in each of the rows on my list page. I'll go off and do some reading as suggested...

suntra

Read about mysql_fetch_array()...

<?php
$query_myResult = "SELECT links.link_url FROM links, tracker WHERE links.id=tracker.link_added";
$myResult= mysql_query($query_myResult, $connLinks) or die(mysql_error());
while($arrResult = mysql_fetch_array($myResult)) {
    echo 'The URL is ' . $arrResult['link_url'] . "<br />\n";
}
?>

cisplatin

Sorry for late response....

Finally got it sorted with your help - Thanks Guys!!!

      <?php
$query_myResult = "SELECT links.link_url FROM links, tracker WHERE links.id=tracker.link_added and tracker.link_added='" . addslashes($row_rsTracker['link_added']) ."' limit 1";
$myResult= mysql_query($query_myResult, $connLinks) or die(mysql_error());
while($arrResult = mysql_fetch_array($myResult)) {
    echo $arrResult['link_url'] . "<br />\n";
}
?>

I'll mark this as resolved in the froum 😃

justsomeone

Glad you got there. 😃

It's been a very useful learning experience for ya.