Page 1 of 1

Last string/entry in array from SQL

Posted: Sun Sep 04, 2005 7:10 pm
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

Posted: Sun Sep 04, 2005 9:36 pm
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`

Posted: Sun Sep 04, 2005 11:11 pm
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

Posted: Sun Sep 04, 2005 11:13 pm
by feyd
if you didn't realize it, those were two separate things: a question and an alternate solution.

Posted: Mon Sep 05, 2005 11:59 am
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

Posted: Mon Sep 05, 2005 12:16 pm
by John Cartwright
Feyd was refering to use mysql ability to auto increment :?

Posted: Mon Sep 05, 2005 2:02 pm
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.