How to obtain last id number, to submit in Picasso for App..

[dave204]

Hi there,

I have a PHP script, which is used to extract id's of uploaded images in a database. I then use id=1 etc tp display the

relevent image using Picasso library in an Android App.

However my customer will be adding images to the database, which will be auto-incremented by id number, but I want to

display the LATEST image ie the highest id number.

How do I achieve this in this URL? Here is my code for the file_display.php

I want to refresh last 12 images if possible, ie each new entry should be added from id=1 to id=12, but I don't know how to

achieve this. That's why I want to obtain LAST ID, and use this in URL below..

THANKs!!

 

<?php
 include "file_constants.php";
 // just so we know it is broken
 error_reporting(E_ALL);
 // some basic sanity checks
 if(isset($_GET['id']) && is_numeric($_GET['id'])) {
     //connect to the db
     $link = mysql_connect("$host", "$user", "$pass")
     or die("Could not connect: " . mysql_error());

     // select our database
     mysql_select_db("$db") or die(mysql_error());

     // get the image from the db
     $sql = "SELECT image FROM test_image WHERE id=" .$_GET['id'] . ";";

     // the result of the query
     $result = mysql_query("$sql") or die("Invalid query: " . mysql_error());

     // set the header for the image
     header("Content-type: image/jpeg");
     echo mysql_result($result, 0);

     // close the db link
     mysql_close($link);
 }
 else {
     echo 'Please use a real id number';
 }
?>

 

AND URL inside Android app:   "http://padihamcars.com/file_display.php?id=1"

[benanamen]

You are using obsolete Mysql code that will not work in the latest version of php and are open to SQL injection. You need to use PDO with prepared statements.

 

But to answer your question:

SELECT image
FROM test_image
ORDER BY id DESC
LIMIT 12

Edited October 5, 2015 by benanamen

[dave204]

Thanks. I added this line to file_display.php:

 

 $sql = "SELECT image FROM test_image ORDER BY id DESC
LIMIT 12 WHERE id=" .$_GET['id'] . ";";

 

However, it failed to download any image to App???

[mac_gyver]

the code you posted at the start of this thread is responsible for dynamically outputting a (one) requested image to the browser/client. you would not change this code (other than to update it and make it secure.)

 

the code you would change is where the image links are being produced.

[benanamen]

Thats not the query I gave you. So what is it you want? You want to display the last 12 images or the last single image?

 

What I gave you will give you the last 12 images.

Edited October 5, 2015 by benanamen

[dave204]

At this stage, I'm looking to get the last image - my current Android code just covers that (although replication, or a For/While loop will easily

add id=2, id=3 in the http statement. However, if I understand correctly, $GET(id) would retrieve the 12 id's - then my http statement would display

id=1 (or in this case the inverse, id=12, or whatever is last). Have I understood it right?

[benanamen]

This is all you need to get the last image. You are only getting one row. There is no loop needed.

SELECT image FROM test_image ORDER BY id DESC
LIMIT 1

<?php
$sql = "SELECT image FROM test_image ORDER BY id DESC LIMIT 1";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_row($result);
?>
<img src="<?= {$row['image']} ?>"> 

Edited October 5, 2015 by benanamen

[dave204]

Thanks for that last post - I'm very grateful - but I need an 'id' number as the result, which I can then use in the URL I illustrated.

Although I appreciate the clarity of your answer, it doesn't fit my current code (which gives me dynamic access to the id's).

To explain, in this line:  $sql = "SELECT image FROM test_image WHERE id=" .$_GET['id'] . ";";

 

which then gives me access to the id at the end of this URL:  "http://padihamcars.c...splay.php?id=1" (where the query for id can be id=1, id=2, id=3, etc.

 

How can I achieve what you're proposing around my "Select image.. as above???

Many thanks once again!

[mac_gyver]

perhaps you missed this information -

the code you posted at the start of this thread is responsible for dynamically outputting a (one) requested image to the browser/client. you would not change this code (other than to update it and make it secure.)

 

the code you would change is where the image links are being produced.

 

this assumes you are dynamically producing the html where the images links are at using php code.

[dave204]

Just to add to above, submitting id=1,id=2, etc as a query from the URL line, fits the 'GET $id' inside the php file. So I need to be able to submit a query

to the SELECT line, based on id number..

[benanamen]

Oh goodness, so not complicated.

 

if you need the id for the image for a url just select the id as well as the image. This will get your URL for for the last one image. Confused about you mentioning 12 images. If you want the last X amount of  images you will need to change the code for multiple rows.

 

<?php
$sql = "SELECT id, image FROM test_image ORDER BY id DESC LIMIT 1";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_row($result);
?>
<a href="http://padihamcars.com/file_display.php?id=<?= {$row['id']} ?>"><img src="<?= {$row['image']} ?>"></a>

Edited October 6, 2015 by benanamen

[dave204]

Hi there!

I have a script (which works), for obtaining the first (or any id number submitted) image from a database.

But recently, I asked on here how to obtain the LAST id from this database ie most recent image.

I was given a very good solution, but I'm not 100% sure how to correctly add this to my current script,

and then using the result in a URL I am using within an Android app I've developed.

Can anyone give me the full, altered script from my original, and solution below, and also what I should then add to the end

of the URL (in place of ?id=1..

Many thanks in advance!!

 

ORIGINAL PHP SCRIPT:

 

<?php
 include "file_constants.php";
 // just so we know it is broken
 error_reporting(E_ALL);
 // some basic sanity checks
 if(isset($_GET['id']) && is_numeric($_GET['id'])) {
     //connect to the db
     $link = mysql_connect("$host", "$user", "$pass")
     or die("Could not connect: " . mysql_error());

     // select our database
     mysql_select_db("$db") or die(mysql_error());

     // get the image from the db
     $sql = "SELECT image FROM test_image WHERE id=" .$_GET['id'] . ";";

     // the result of the query
     $result = mysql_query("$sql") or die("Invalid query: " . mysql_error());

     // set the header for the image
     header("Content-type: image/jpeg");
     echo mysql_result($result, 0);

     // close the db link
     mysql_close($link);
 }
 else {
     echo 'Please use a real id number';
 }
?>
 

SOLUTION:

 

<?php
$sql = "SELECT image FROM test_image ORDER BY id DESC LIMIT 1";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_row($result);
?>
 

CURRENT URL:

 

"http://padihamcars.com/file_display.php?id=1"

[Ch0cu3r]

The code labeled "ORIGINAL PHP SCRIPT" is only for fetching the an image by id from the database where a row matches the id in the url and displays that image. The code labeled "SOLUTION" is for getting the last image image from the database (which will be the newest image).

 

Are you want to display two images at the same time, the requested id image and the newest image together?

[benanamen]

Dude, why did you start a whole new thread on the same thing?

[Barand]

Topics merged.