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

cisplatin · Feb 8, 2006

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,

R

bpat1434 · Feb 8, 2006

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 · Feb 8, 2006

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,

R

bpat1434 · Feb 8, 2006

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 · Feb 8, 2006

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 · Feb 9, 2006

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 · Feb 9, 2006

Thanks for that justsomeone,

sounds as though i should be able to manage that.

No doubt i'll be back if i fail

cisplatin · Feb 10, 2006

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.

R

justsomeone · Feb 10, 2006

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 · Feb 10, 2006

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

cisplatin · Feb 15, 2006

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 · Feb 15, 2006

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 · Feb 15, 2006

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 · Feb 15, 2006

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 · Feb 15, 2006

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 · Feb 27, 2006

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 · Feb 27, 2006

Glad you got there.

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

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

Ccisplatin

bpat1434

Ccisplatin

bpat1434

Ccisplatin

Jjustsomeone

Ccisplatin

Ccisplatin

Jjustsomeone

Jjustsomeone

Ccisplatin

bpat1434

Jjmosterb

Ccisplatin

Ssuntra

Ccisplatin

Jjustsomeone