Problem with MAX()

[seaweed]

I have a table that uses an auto-increment field, and requires a second auto-increment, which isn't allowed in MySQL. So I tried to query the db for the 2nd variable, grab the highest value and add 1, but it will not work with a $variable in the query string. Any idea why? And before you ask, I need the second auto-increment because the data from this table is being split into two display sections, each needs to be user-sorted with a toggle. Also, $tableselect is passed a value, from the $_POST method.

 

This works:

 

 // 
$maxTable = $_SESSION['username'] . "_projects";

$maxResult = mysql_query("SELECT MAX(project_id) FROM $maxTable")
     or die(mysql_error());

     while($row = mysql_fetch_array( $maxResult )) {
          $maxdata = $row['MAX(project_id)'];
  $maxdata1 = $maxdata + 1;
}

 

This does not work:

 

 // 
$maxTable = $_SESSION['username'] . "_projects";
$maxField = $tableselect . "_id";

$maxResult = mysql_query("SELECT MAX($maxField) FROM $maxTable")
     or die(mysql_error());

     while($row = mysql_fetch_array( $maxResult )) {
          $maxdata = $row['MAX($maxField)'];
  $maxdata1 = $maxdata + 1;
}

[trq]

You shouldn't be relying on any auto incrementing field to sort data, auto incrementing fields are designed to keep your data's integrity. maybe if you explain what your trying to do in more detail we could offer a better suggestion or at minimum post and point out the relevant code. i don't see any correlation to your problem in the code you have posted.

[seaweed]

Why wouldn't you sort by an auto-incrementing field? Applications like phpBB and WordPress do it all the time with postID etc. phpBB sorts their memberlist by the auto-incrementing field 'userid' by default.

 

My question is, why does SELECT MAX(project_id) work and SELECT MAX($maxField) not work, when they technically hold the same value?

 

 

[drisate]

max(project_id) stands for the value of the current row and max($project_id) uses and external value as SQL does not set $project_id vars. In other words, max($project_id) is never gona work.

[seaweed]

The why does $maxTable work for the table name?

 

You're saying that a MySQL query cannot except a variable as an argument? That's wrong.

[drisate]

You're saying that a MySQL query cannot except a variable as an argument? That's wrong.

 

Never said that bro

 

But i have to admit i did'int look your code, only your question ad thought you where trying to use a $ var as a current row value ... never mind

[sasa]

change

$maxdata = $row['MAX($maxField)'];

to

$maxdata = $row["MAX($maxField)"];

[corbin]

Or you could use an alias.

 

 

IE:

 

 

$maxResult = mysql_query("SELECT MAX(project_id) as max_proj_id FROM $maxTable");

 

 

Then use ['max_proj_id'].

[seaweed]

There's various different $maxField options coming in from the previous page via $_POST, so I shove it in a variable and then I want to use that to query all of the rows with that $maxField value, get all of their unique ID's, find the highest value with MAX() and ++ its butt. It's like this:

 

It's a ticketing system, like for a help desk, and there are many different kinds of tickets, four to be exact. I have the page that displays all of the tickets broken into four tabbed sections, because I have four employees, each with their own expertise. So there's a UNIX set of tickets, Mac set, Winblows set and a handheld set. The problem is, if I have all of these tickets in one table, and each ticket has a unique ID, say like '35', and I auto-increment it, these numbers are shared across all four sections. This means the Unix set could have ticker number 35, and the Mac section will never have a 35. I would like each of them to have their own auto-incremented number, so the Unix set could have a ticket #5 an the Windows set have their own Ticket #5... Maybe the auto-increment fields are uid, mid, wid, hid etc... BUT MySQL only allows one auto-increment per row.

 

Man, I hope this makes sense!

 

My opther solution is to just spread the unique ID's across all four sections, but it's my 2nd choice.