• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1137
  • Last Modified:

MySQL get next unique value without auto increment

I found this page
http://dba.stackexchange.com/questions/19019/mysql-get-next-unique-value-without-auto-increment
that has exactly what i needed. it however has errors when i try and use the code ive made

let me explain what im doing -
im trying to get in my `column_no` the last number then find under that the last `order` number for a desktop grid im making with column 1,2,3 and then rows or order under each column.
now i run this query in myphpadmin it works just fine
myphpadmin screenshot with query and table outputmy code is
include ('mysqli_ctx.php');

//$strSQL = "SELECT column_no FROM dashboard_items WHERE user_id='1' ORDER BY column_no ASC";
$strSQL = "SELECT `column_no`,MAX(`order`) FROM `dashboard_items` WHERE `user_id`='1' GROUP BY `column_no`";
$result = mysqli_query($mysqli,$strSQL)or trigger_error("Query Failed! SQL: $strSQL - Error: ".mysqli_error($mysqli), E_USER_ERROR);
/*
echo "<pre>";
var_dump($result);
echo "</pre>";
die('stop');
*/
$counter = 0;
while($row = mysqli_fetch_assoc($result)) {
     //echo "#".$counter.": ".$row[0]."<br>";
    //$counter++;
    echo "<pre>";
    var_dump($row);
    echo "</pre>";

}
die('stop');
/*
column_no	MAX(`order`)
1		2
2		4
3		2

*/

Open in new window


mysql sql for ref and testing is
CREATE TABLE `dashboard_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `column_no` int(11) NOT NULL,
  `order` int(11) NOT NULL,
  `last_update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `dashboard_items`
--

INSERT INTO `dashboard_items` (`id`, `column_no`, `order`, `user_id`) VALUES
(1, 1, 0, 1),
(2, 1, 1, 1),
(3, 1, 2, 1),
(4, 3, 1, 1),
(5, 2, 0, 1),
(6, 2, 1, 1),
(7, 2, 3, 1),
(8, 2, 4, 1),
(9, 3, 2, 1),
(10, 2, 2, 1),
(13, 3, 0, 1);

Open in new window


my current output is
array(2) {
  ["column_no"]=>
  string(1) "1"
  ["MAX(`order`)"]=>
  string(1) "2"
}
array(2) {
  ["column_no"]=>
  string(1) "2"
  ["MAX(`order`)"]=>
  string(1) "4"
}
array(2) {
  ["column_no"]=>
  string(1) "3"
  ["MAX(`order`)"]=>
  string(1) "2"
}
stop

Open in new window


how do i get an output of
column_no	MAX(`order`)
1		2
2		4
3		2

Open in new window


and how can i make an output of
column_no_next   order_next 
     1            2
     2            5
     3            3

Open in new window


to be able to make php vars so i can use that to make my next available entries?

thank you in advance for any code or help you may provide
Johnny
0
Johnny
Asked:
Johnny
  • 13
  • 5
1 Solution
 
Ray PaseurCommented:
What is the part you need help with?  Are the queries finding the right data?
0
 
JohnnyAuthor Commented:
@ray
always nice to see you field a post!

im looking to get the next order number available for each column so i can add the next item

the code i posted works fine. i just didnt understand the way the fellow had it for
SELECT @nextworkordernum
plus i have 3 columns and has to be parsed by user_id, so i do not understand how to get it to give me the values so i can use them in a mysqli(insert query) for the next item
note: the code i have works fine, just unsure how to get ["MAX(`order`)"]=>  string(1) "4" value back as a $row['????'] in a while loop
0
 
JohnnyAuthor Commented:
@ray
what i really need to do is be able to find the shortest(lowest order number in column) column,order for the next injection in this case column 1 row 3 (my next numbers table i made by hand in my example in question post was wrong for column 1) so i can add an item in the spot 1,3
and make it a insert command
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
JohnnyAuthor Commented:
so i can execute something like
not sure this is correct (if i did this wrong please tell me)

as we would know the column number is 1 and the user id is 1 and we found the next available slot of 3 in the order field
INSERT INTO dashboard_items (`order`,`item`) VALUES(3),('some  item name') WHERE user_id='1' AND `column_no`='1' 

Open in new window

0
 
Ray PaseurCommented:
Let me set something up to test with.  Back in a moment.
0
 
Ray PaseurCommented:
I think we are part of the way there.  Have a look at this:
http://iconoun.com/demo/temp_pern.php

Moving parts start at line 86
<?php // demo/temp_pern.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';

// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE `dashboard_items` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `column_no` int(11) NOT NULL,
  `order` int(11) NOT NULL,
  `last_update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` int(11) NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
"
;
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// LOADING TEST DATA INTO THE TABLE
$sql
=
"
INSERT INTO `dashboard_items` (`id`, `column_no`, `order`, `user_id`) VALUES
(1, 1, 0, 1),
(2, 1, 1, 1),
(3, 1, 2, 1),
(4, 3, 1, 1),
(5, 2, 0, 1),
(6, 2, 1, 1),
(7, 2, 3, 1),
(8, 2, 4, 1),
(9, 3, 2, 1),
(10, 2, 2, 1),
(13, 3, 0, 1)
"
;
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// RUN A QUERY AGAINST THE TEST DATA BASE TABLE
$sql = "SELECT `column_no`, MAX(`order`) AS max FROM `dashboard_items` WHERE `user_id`='1' GROUP BY `column_no`";
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
while ($row = $res->fetch_object())
{
    echo PHP_EOL;
    echo "column_no: $row->column_no, max: $row->max";
}

Open in new window

0
 
JohnnyAuthor Commented:
@ray
yep VERY close
can we do it with
while($row = mysqli_fetch_assoc($result)) {

Open in new window

please as object php ( i think that's what its called),pdo and classes confuse me and im still trying to learn(wrap my head around them) i messed up when i first started to learn and should have went that route, i also thought perl would take over php but it has not(heck i think i forgot perl now its been so long)

thank you
0
 
JohnnyAuthor Commented:
hrrrm that dont work
$strSQL = "SELECT `column_no`,MAX(`order`) FROM `dashboard_items` WHERE `user_id`='1' GROUP BY `column_no`";
$result = mysqli_query($mysqli,$strSQL)or trigger_error("Query Failed! SQL: $strSQL - Error: ".mysqli_error($mysqli), E_USER_ERROR);

while($row = mysqli_fetch_assoc($result))
     {
         echo "column_no: ".$row['column_no']." - ". $row['max']."<br>";
}

Open in new window

whats the second value need to be for max?
0
 
JohnnyAuthor Commented:
ok go figure this works
echo "column_no: ".$row['column_no']." - ". $row['MAX(`order`)']."<br>";

Open in new window

0
 
JohnnyAuthor Commented:
ok so i go this to work

//$strSQL = "SELECT column_no FROM dashboard_items WHERE user_id='1' ORDER BY column_no ASC";
$strSQL = "SELECT `column_no`,MAX(`order`) FROM `dashboard_items` WHERE `user_id`='1' GROUP BY `column_no`";
$result = mysqli_query($mysqli,$strSQL)or trigger_error("Query Failed! SQL: $strSQL - Error: ".mysqli_error($mysqli), E_USER_ERROR);

while($row = mysqli_fetch_assoc($result))
     {
         $next_order = intval($row['MAX(`order`)'])+1;
         echo "column_no: ".$row['column_no']." - ". $row['MAX(`order`)']." - next order number ".$next_order."<br>";
}

Open in new window


so how would i determine that 1,3 is the shortest column slot plz?
0
 
JohnnyAuthor Commented:
oh out put is now
column_no: 1 - 2 - next order number 3
column_no: 2 - 4 - next order number 5
column_no: 3 - 2 - next order number 3
stop

Open in new window

0
 
JohnnyAuthor Commented:
we would also need to know the col its on so would an array like

$strSQL = "SELECT `column_no`,MAX(`order`) FROM `dashboard_items` WHERE `user_id`='1' GROUP BY `column_no`";
$result = mysqli_query($mysqli,$strSQL)or trigger_error("Query Failed! SQL: $strSQL - Error: ".mysqli_error($mysqli), E_USER_ERROR);

while($row = mysqli_fetch_assoc($result))
     {
         $next_order[$row['column_no']] = intval($row['MAX(`order`)'])+1;
         echo "column_no: ".$row['column_no']." - ". $row['MAX(`order`)']." - next order number ".$next_order[$row['column_no']]."<br>";
}
echo " min: ", min($next_order)."<br>";
die('stop');

Open in new window


outputs
column_no: 1 - 2 - next order number 3
column_no: 2 - 4 - next order number 5
column_no: 3 - 2 - next order number 3
min: 3
stop

Open in new window


tells me the lowest number but not what col it is. hrrrm
0
 
JohnnyAuthor Commented:
hrrm still cant get pattern for column_no and order lowest number.
tried:
$strSQL = "SELECT `column_no`,MAX(`order`) FROM `dashboard_items` WHERE `user_id`='1' GROUP BY `column_no`";
$result = mysqli_query($mysqli,$strSQL)or trigger_error("Query Failed! SQL: $strSQL - Error: ".mysqli_error($mysqli), E_USER_ERROR);
$counter = 1;
while($row = mysqli_fetch_assoc($result))
     {
         $next_order[$counter][$row['column_no']] = intval($row['MAX(`order`)'])+1;
         echo "column_no: ".$row['column_no']." - ". $row['MAX(`order`)']." - next order number ".$next_order[$counter][$row['column_no']]."<br>";
$counter++;
}
$min = min($next_order);
echo "<pre>";
print_r ($min);
echo "</pre>";
echo "min ".$min[0][0]."<br>";
die('stop');

Open in new window


outputs
column_no: 1 - 2 - next order number 3
column_no: 2 - 4 - next order number 5
column_no: 3 - 2 - next order number 3
Array
(
    [3] => 3
)
min 
stop

Open in new window

0
 
Ray PaseurCommented:
OK, a few things to try and help.  Let's deconstruct this:

$sql = "SELECT `column_no`, MAX(`order`) AS max FROM ...

The SELECT query will create a results set consisting of rows of data.  Each row of data will have array indexes (or object properties) that reflect the column names in the table(s) you selected.  Unless you use the AS clause, to change the index or property name.  The reason I did that was because writing an array index like MAX(`order`), while technically accurate, is harder for me to type and read than just max.

Using the fetch_object() method changes the way we address the rows of data, and I think you'll agree that it changes it for the better.  Using fetch_assoc() you get rows that are arrays and that require this kind of punctuation:

$row['max']

But if you use fetch_object() you get rows that are objects, and each data element saves two keystrokes.

$row->max

Saving two keystrokes might not sound like much, but that's not all the benefit you get.  The object-oriented notation does not need to be escaped or encapsulated to use it in a quoted string.  So you reduce your risk of injecting parse errors because you're not doing as much typing.  You can use the fetch_object() method to retrieve your rows with either the object-oriented MySQLi or the procedural MySQLi.
http://php.net/manual/en/mysqli-result.fetch-object.php

On the question of the program logic and the test data, I'm not sure exactly what we're trying to get.  I see that you're getting a "min" of 3, but that is ambiguous with respect to the column number.

Do you have error_reporting(E_ALL) set in your script?
0
 
JohnnyAuthor Commented:
i got it
//$strSQL = "SELECT column_no FROM dashboard_items WHERE user_id='1' ORDER BY column_no ASC";
$strSQL = "SELECT `column_no`,MAX(`order`) FROM `dashboard_items` WHERE `user_id`='1' GROUP BY `column_no`";
$result = mysqli_query($mysqli,$strSQL)or trigger_error("Query Failed! SQL: $strSQL - Error: ".mysqli_error($mysqli), E_USER_ERROR);
$counter = 1;
while($row = mysqli_fetch_assoc($result))
     {
         $next_order[$counter][$row['column_no']] = intval($row['MAX(`order`)'])+1;
         echo "column_no: ".$row['column_no']." - ". $row['MAX(`order`)']." - next order number ".$next_order[$counter][$row['column_no']]."<br>";
$counter++;
}
//$min = min($next_order);
echo "<pre>";
var_dump(min($next_order));
echo "</pre>";
foreach( max($next_order) as $key => $value) {
  //echo $key; //
  $found_col_n = $key;
  //echo $value; // 
  $found_order_no = $value;
}
echo "found set col/order".$found_col_n.",". $found_order_no."<br>";


$i_strSQL = "INSERT INTO dashboard_items (`column_no`,`order`,`content`,`user_id`) VALUES('".$found_col_n."','".$found_order_no."', 'some dum content', '1')";
echo $i_strSQL;
$result = mysqli_query($mysqli,$i_strSQL)or trigger_error("Query Failed! SQL: $i_strSQL - Error: ".mysqli_error($mysqli), E_USER_ERROR);
echo "<br>";
die('stop');
[[code]

output
[code]
olumn_no: 1 - 2 - next order number 3
column_no: 2 - 4 - next order number 5
column_no: 3 - 2 - next order number 3
array(1) {
  [3]=>
  int(3)
}
found set col/order1,3
INSERT INTO dashboard_items (`column_no`,`order`,`content`,`user_id`) VALUES('1','3', 'some dum content', '1')
stop

Open in new window


so i found my col 1 and order 3 as my shorted - col (1) and smallest order number (2 - 3 being the slot thats next open)
i then used this to build my item insert query so that i now have the next available slot on the desktop (or item rows)
does that make sense?

@ray
as always ray thank you
not too sure this time as i dont think i would have gotten it with out you Ray, kinda walked me a bit with it.. so should i give you the points?? kinda answered it myself didnt i?
await your reply to this points question.
0
 
JohnnyAuthor Commented:
@ray OH as to the object thing, as i said im learning it, just super slow for me to wrap my head around it, id relaly like to do pdo for sql and have object/class's as the code... i see how powerfull they are, but if i dont understand it its not gonna work. i guess like everything else i should be testing trial and error.
ill try and do my next project this way with pdo and class with object
0
 
JohnnyAuthor Commented:
thx again
0
 
Ray PaseurCommented:
Thanks for the points.

If you're interested in MySQLi and PDO, this article maps the familiar but obsolete MySQL extension to the newer object-oriented extensions.  
http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

You might find this useful, too:
http://iconoun.com/mysql_mysqli_pdo_function_map.php
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 13
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now