[SOLVED] Mysql Functions

[Ninjakreborn]

I have been lately trying to do mysql functions.  I am just wondering why none of them have not been working, is there something I am doing wrong.

I have tried using sum (the total amount of what is in rows)

and LAST which is meant to get the last row that was entered, but nothing is working.

$lastbills = "SELECT LAST(amount) FROM transactions WHERE type = 'Bill' AND userid = '" . $_SESSION['cymember'] . "' LIMIT 1;";

$lastbillq = mysql_query($lastbills);

echo $lastbillsq;

This is just one example of me trying to get the last occurence of something.

I know queries aren't always the easiest to work with, but some of this stuff would be much simpler if I could get the hang of sql based specific functions that I can use straight in the query.  Saving unnecessary time, I just have to learn to use them properly, any advice is greatly appreciated.  Thanks.

 

[Wildbug]

Is LAST() even a function?

 

You can use LAST_INSERT_ID() for AUTO_INCREMENT columns if you're looking for that number, but it's only good after your latest INSERT, and it doesn't look like that's what you're trying for.

 

You should have a date column or id column for that kind of data, so why don't you use an ORDER BY on that column?

 

I.e.,

 

SELECT whatever FROM transaction WHERE type='Bill' AND userid='$_SESSION[cymember]' ORDER BY date DESC LIMIT 1

// or

SELECT whatever FROM transaction WHERE type='Bill' AND userid='$_SESSION[cymember]' ORDER BY id DESC LIMIT 1

[Ninjakreborn]

Last insert id is one of php's Mysql functions.  I am speaking directly of SQL's built in functions that are native to the SQL language itself.

These are used inside the queries themselves.  Yes last is one of those functions.

 

[Wildbug]

LAST_INSERT_ID() is a built-in MySQL function.  mysql_insert_id() is the PHP function.

 

Can you show me the MySQL manual page for the LAST() function?  I can't seem to find it.

[Ninjakreborn]

I meant mysql_insert_id() however it was very perceptive that you saw that.

mysql_insert_id is the one I currently use to normally get the last id.

http://www.w3schools.com/sql/sql_functions.asp

That is the page I saw a list of "sql functions" and I have been trying to make use of some of them for some time.  However every time I try, it's just now there.  It just does not work, no matter how I try to setup the query it just doesn't want to use those functions.

[Wildbug]

This is a list of MySQL's built-in functions.  That w3schools page seems to have general SQL functions; any given SQL server is not guaranteed to have the same functions or the same names for them, and LAST() is not listed as a MySQL function.

 

Like I said previously, your best bet for returning the result you expect is to use another column's value to get the last entry -- either a date or an auto increment column.

[Wildbug]

Also, there's some problems with code you posted originally:

<?php
$lastbills = "SELECT LAST(amount) FROM transactions WHERE type = 'Bill' AND userid = '" . $_SESSION['cymember'] . "' LIMIT 1;";
$lastbillq = mysql_query($lastbills);
echo $lastbillq; // I changed this from $lastbillsq
?>

 

The $lastbillq won't contain the value returned from the query; it will contain a resource id refering to the results, if any.  You need to use either mysql_fetch_row() or mysql_result().

 

<?php
$row = mysql_fetch_row($lastbillq);
echo $row[0];

// ...or...

echo mysql_result($lastbillq,0);
?>

 

I like the second one for returning a single value from a query -- like what you're doing.

[Ninjakreborn]

I have all the "basics, and imtermediate, and a lot of the advanced" stuff mastered.

As far as building using queries, it's just that list of SQL functions always seemed like it would cut off some time on the programming.

THe code I showed you were just a few 2 minute example I threw up there to explain what I was trying to say, because I was trying to play around with some of those SQL only functions.

 

However in the end you answered my question anyway, I was aware that those were SQL functions, but I didn't know that mysql didn't support all of them.

That is why I have never been able to get those specifically to work.

 

Now that I know this, in the future when working with any database system (postgresql, dbkwik, mysql, or anything) I will make sure I find a list of functions they support so I make sure I don't waste my time with specific SQL related functions that don't work with all database systems.

Thanks for the advice.

[Wildbug]

Glad to have answered your problem, however roundaboutly.  And sorry to have been too picky or beginner-oriented, but you never know when someone's problem isn't what they claim it is.

[Ninjakreborn]

No, actually you brought me to a few very strong notices.  For one, I realized that there are different type of classifications and I never took the time to really decide.

There are sql specific functions, and mysql specific funcitons, and php's mysql functions. I was aware of this but never took the time to really look at the differences, and analyze which are best in what situations.

Because of that I am going to sit back and look at some of those, as well as that list.

It's going to be a very valuable resource.

So in actually you helped a lot, I think I actually went up a whole level with sql/mysql related knowledge thanks to you.  I never ever realized those mysql functions existed, because I was always too busy looking at just those sql functions, thanks again for the advice.