[RESOLVED] Generate URLs that use most recent auto increment

bemore · Jan 28, 2013

On the same page that an INSERT INTO takes place, I need to grab that newly created row's auto increment value to store a unique URL inside another column on the same row, and have an image to represent the url.

I figure that after the data submission, a new query could be made to get the newest id (using mysqli_insert_id) and place the id at the end of a url, then INSERT or UPDATE that url into a column.. is that right?

I've tried a few things, but I either get errors, or no errors but no UPDATE or INSERT happens. Here is what I was just messing with.

<?php
//Grab new insert ID
$id=mysqli_insert_id($link);
?>
<input type="radio" name="editurl" value="http://www.somesite.com/somefile.php?variable=$id" style="display:none;" checked="checked">
<?php
//Insert value containing specific ID
$tbl_name="opl_comp";
$editurl=$_POST['editurl'];
$editlink="UPDATE $tbl_name SET edit='$editurl' WHERE id='$id'";
$result=mysqli_query($link,$editlink) or die("Error: ".mysqli_error($editlink));
?>

I was trying with this, hoping to at least get the URL into the database, then go from there.. though even if it worked, I'd still be unsure how to get an image to display in place of text.

Any tips on accomplishing this would be helpful

proudfist · Jan 28, 2013

I don't really understand what you want to do. But I guessed and wrote some code, hope it might help you in some way..

<?php
# some insert query takes place

# fetch last inserted id
$lastID = $mysqli->insert_id;

# create >unique url<
$url = "http://www.somesite.com/somefile.php?variable=$lastID";

# store >unique url<
$q = $mysqli->query("UPDATE opl_comp SET edit='$url' WHERE id='$lastID'");
?> 
<input type="radio" name="editurl" value="<?php echo $url; ?>" style="display:none;" checked="checked">

bradgrafelman · Jan 28, 2013

My tip would be to ask: Do you actually need to do this? If the only variable piece of data in the URL is the ID value, then can't you generate the URL yourself simply by SELECT'ing the ID value? It seems silly to store both "This row is ID #123" as well as "The URL for this row is exactly the same as all other rows but with ID 123 at the end."

bemore · Jan 28, 2013

proudfist;11023233 wrote:

I don't really understand what you want to do. But I guessed and wrote some code, hope it might help you in some way..

<?php
# some insert query takes place

# fetch last inserted id
$lastID = $mysqli->insert_id;

# create >unique url<
$url = "http://www.somesite.com/somefile.php?variable=$lastID";

# store >unique url<
$q = $mysqli->query("UPDATE opl_comp SET edit='$url' WHERE id='$lastID'");
?> 
<input type="radio" name="editurl" value="<?php echo $url; ?>" style="display:none;" checked="checked">

Thanks You guessed the process correctly. I thought I explained it alright, though xD but the code is giving me an error.
Fatal error: Call to a member function query() on a non-object

It's referring to

$q = $mysqli->query("UPDATE opl_comp SET edit='$url' WHERE id='$lastID'");

bradgrafelman;11023239 wrote:
My tip would be to ask: Do you actually need to do this? If the only variable piece of data in the URL is the ID value, then can't you generate the URL yourself simply by SELECT'ing the ID value? It seems silly to store both "This row is ID #123" as well as "The URL for this row is exactly the same as all other rows but with ID 123 at the end."

Thank you for the possible method. I too thought it seemed weird to have to generate a unique URL and then submit the entire thing, but I was thinking it might be the only way to do this.. Is it possible to, while looping just the ID column, somehow attach a URL prefix to it? If so, then the other thing is that I'd like those links to be represented by an image.. also possible?

bradgrafelman · Jan 28, 2013

bemore;11023281 wrote:
Is it possible to, while looping just the ID column, somehow attach a URL prefix to it?

Of course; it's possible to output any arbitrary data you want before you output the data you retrieved from the database.

bemore;11023281 wrote:
If so, then the other thing is that I'd like those links to be represented by an image.. also possible?

Yes, it's possible to add a hyperlink to an image in an HTML document.

bemore · Jan 28, 2013

bradgrafelman;11023289 wrote:
Yes, it's possible to add a hyperlink to an image in an HTML document.

But what about assigning the primary key ID to the end of each prefix while looping the column?

<?php
$link = 'somesite.com/somefile.php?id=$rowsID';
$img = 'edit.gif';
echo '<a href="'. $link .'"><img style="border:none;" src="'. $img .'" /></a>';
?>

$rowsID needs to represent the primary key value of each row during the loop of data.. so each link is unique to each row.. How can I go about this?

bradgrafelman · Jan 29, 2013

bemore;11023293 wrote:
$rowsID needs to represent the primary key value of each row during the loop of data.. so each link is unique to each row.. How can I go about this?

You'd go about it exactly as you did above, only you would either want to use concatenation or use double quotes rather than single quotes to delimit the string (since variable interpolation isn't performed inside strings delimited with single quotes). You'd also, of course, change '$rowsID' to whatever variable actually holds the ID value for the row being processed.

bemore · Jan 29, 2013

bradgrafelman;11023311 wrote:
You'd go about it exactly as you did above, only you would either want to use concatenation or use double quotes rather than single quotes to delimit the string (since variable interpolation isn't performed inside strings delimited with single quotes). You'd also, of course, change '$rowsID' to whatever variable actually holds the ID value for the row being processed.

Ah, good to know I was on the right track. I guess what I am trying to do, then, is create a variable to display the ID value correctly. So far everything I've tried doesn't display anything in place of the $rowsID variable.

<?php
$rowsID = $rows['id'];
$editlink = 'http://www.oplinfo.x11s.org/opllist/update.php?game_id="$rowsID"';
$src = 'http://www.oplinfo.x11s.org/files/images/edit.gif';
echo '<a href="'. $editlink .'"><img style="border:none;" src="'. $src .'" /></a>';
?>

Does the problem lie in the " " on $rowsID? I also tried "'. $rowsID.'".

bradgrafelman · Jan 29, 2013

Well what's the actual context of that code? I'm assuming you're actually trying it in a much larger script, e.g. one that connects to a SQL database, executes a query, processes a result set, etc.

Also you still have the same issue I mentioned above - you're trying to use $rowsID inside of a string that is delimited by single quotes. (The only difference is that now you've added literal double quotes inside that string.) See the Variable parsing section of the [man]string[/man] manual page for more info/examples.

bemore · Jan 29, 2013

See the Variable parsing section of the string manual page for more info/examples.

Thank you

Well what's the actual context of that code? I'm assuming you're actually trying it in a much larger script, e.g. one that connects to a SQL database, executes a query, processes a result set, etc.

Okay, I'll post the whole thing, minus the CSS markup.

  <?php
$link = mysqli_connect('host','user','pass','database');
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
$tbl_name="opl_comp"; // Table name 

$sql="SELECT * FROM $tbl_name ORDER BY gamename";
$result=mysqli_query($link,$sql) or die("Error: ".mysqli_error($link));
?>

  <?php
while($rows = mysqli_fetch_array($result, MYSQLI_BOTH))
{
?>
<div id="wrapper">
<div id="content">
  <div id="comp"><center><img src="<?php echo $rows['comp']; ?>" align="center"; ?></center></div>
		<div id="gamename">&nbsp;<? echo $rows['gamename']; ?></div>
        <div id="region"><center><img src="<?php echo $rows['region'];?>" align="center"; ?></center></div>
		<div id="modes"><center><? echo $rows['mode']; ?></center></div>
        <div id="vmc"><center><? echo $rows['vmc']; ?></center></div>
        <div id="smb"><center><? echo $rows['smb']; ?></center></div>
        <div id="hdd"><center><? echo $rows['hdd']; ?></center></div>
        <div id="usb"><center><? echo $rows['usb']; ?></center></div>
        <div id="ver"><center><? echo $rows['oplver']; ?></center></div>
        <div id="notes">&nbsp;<? echo $rows['notes']; ?></div>
        <div id="edit"><?php
$rowsID = $rows["id"];
$editlink = 'http://www.oplinfo.x11s.org/opllist/update.php?game_id="$rowsID"';
$src = 'http://www.oplinfo.x11s.org/files/images/edit.gif';
echo '<a href="'. $editlink .'"><img style="border:none;" src="'. $src .'" /></a>';
?>

 </div>

</div>
</div>
<?php
}
?>

bradgrafelman · Jan 29, 2013

Assuming you actually do have a column named id in your SQL table, the only problem I see is variable parsing issue I've mentioned above.

bemore · Jan 29, 2013

bradgrafelman;11023325 wrote:
Assuming you actually do have a column named id in your SQL table, the only problem I see is variable parsing issue I've mentioned above.

Indeed you are correct. Thank you

Corrected code

<?php
$rowsID = ($rows['id']);
$editlink = "http://www.oplinfo.x11s.org/opllist/update.php?game_id=$rowsID";
$src = 'http://www.oplinfo.x11s.org/files/images/edit.gif';
echo '<a href="'. $editlink .'"><img style="border:none;" src="'. $src .'" /></a>';
?>

[RESOLVED] Generate URLs that use most recent auto increment

Bbemore

Pproudfist

Bbradgrafelman

Bbemore

Bbradgrafelman

Bbemore

Bbradgrafelman

Bbemore

Bbradgrafelman

Bbemore

Bbradgrafelman

Bbemore