Last string/entry in array from SQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Last string/entry in array from SQL

Post by Zoxive »

Code: Select all

<?php
// Connecting, selecting database
$link = mysql_connect('localhost', 'NSF', 'klownz')
    or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db('blog_') or die('Could not select database');

// Performing SQL query
$query = "SELECT post_id FROM posts";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Printing results in HTML
$line = mysql_fetch_array($result, MYSQL_ASSOC);
  
	print '<br><br>';
	print end($line);



// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($link);
?>
Ok, well i tryed searching, but i couldn't find anything.

I just started learning php a week ago, i understand what most things do, but i can't really comprend everything yet, and it takes me a while to work it out.

Right now im trying to make a couple simple Forms, that will save the info in my "mysql" database, i have the forms, like the subject, and main entry working, but now im trying to get a working ID that will update for each entry, so if it is my first post it would be 1, second 2, etc. I figured i would look for a way to look for the last added number was in mysql, and when i retreve the info, i get it in array form. and i found the end() command to get the last entry in the array, but it isn't working correctly, i get the first entry, and i tryed reversing the array too, but still no luck, but i figured there would be a better way.

Right that php code outputs..

Code: Select all

Connected successfully

1
If i make it a while statement,

Code: Select all

<?php
// Connecting, selecting database
$link = mysql_connect('localhost', 'NSF', 'klownz')
    or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db('blog_') or die('Could not select database');

// Performing SQL query
$query = "SELECT post_id FROM posts";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Printing results in HTML
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
  
	print '<br><br>';
	print_r ($line);
}


// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($link);
?> 
</body>
</html>
and have it put all the of the array i get..

Code: Select all

Connected successfully

Array ( [post_id] => 1 )

Array ( [post_id] => 5 )
But what i want is to get that 5, since it was the last thing i would have submitted to be a variable. Then i could do $id++; that stuff, and have it add when i submit the next one, then have it be submitted with the other code i already have done and working.

Basically what im trying to do, is what they do for php, is for each entry yuo have it gives it a post #, and then later you can call that post by the number.So im trying to get the Last added thing in mysql for "post_id" from table "posts".

-NSF
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

is there a reason you aren't using the database's auto_increment field attribute for post_id ?

Code: Select all

SELECT MAX(`post_id`) + 1 FROM `posts`
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Post by Zoxive »

feyd wrote:is there a reason you aren't using the database's auto_increment field attribute for post_id ?

Code: Select all

SELECT MAX(`post_id`) + 1 FROM `posts`
Thanks this is what i needed : p

-NSF
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

if you didn't realize it, those were two separate things: a question and an alternate solution.
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Post by Zoxive »

feyd wrote:if you didn't realize it, those were two separate things: a question and an alternate solution.
I wanted an alternate solution, apparently you didn't read everything i typed, i just started, last week. So i did not know there was a SELECT MAX..

But i got everything working so im happy.

-NSF
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Feyd was refering to use mysql ability to auto increment :?
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post by ody »

Code: Select all

mysql> create table post(
    -> id int primary key auto_increment
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> 
mysql> insert into post values();
Query OK, 1 row affected (0.00 sec)

mysql> select * from post;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> insert into post values();
Query OK, 1 row affected (0.00 sec)

mysql> select * from post;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql>
the MySQL auto_increment attribute to a table column updates itself by 1 on each insert.
Post Reply