PHP get data/process

[kdjernigan]

$checkgroupid = mysql_query("SELECT group_id FROM messages WHERE to_user = '$to' and from_user = '$from' ORDER BY group_id LIMIT 1");

$cgidcount = "0";
while ($line = mysql_fetch_assoc($checkgroupid)) {
$cgidcount++;
}

if ($cgidcount == "1") {
$cgid1 = mysql_query("SELECT group_id FROM messages WHERE to_user = '$to' and from_user = '$from' LIMIT 1");
while($row = mysql_fetch_array($cgid1)) {
$group_id = $row['group_id'];
}
}
if ($cgidcount == "0") {


$checkgroupid2 = mysql_query("SELECT group_id FROM messages WHERE to_user = '$from' and from_user = '$to' ORDER BY group_id LIMIT 1");

$cgid2count = "0";
while ($line = mysql_fetch_assoc($checkgroupid2)) {
$cgid2count++;
}

if ($cgid2count == "1") {
$cgid2 = mysql_query("SELECT group_id FROM messages WHERE to_user = '$from' and from_user = '$to' ORDER BY group_id LIMIT 1");
while($row = mysql_fetch_array($cgid2)) {

$group_id = $row['group_id'];
}
}
if ($cgid2count == "0") {







// The length we want the unique reference number to be
$unique_ref_length = 10;

// A true/false variable that lets us know if we've
// found a unique reference number or not
$unique_ref_found = false;

// Define possible characters.
// Notice how characters that may be confused such
// as the letter 'O' and the number zero don't exist
$possible_chars = "1234567890";

// Until we find a unique reference, keep generating new ones
while (!$unique_ref_found) {

// Start with a blank reference number
$unique_ref = "";

// Set up a counter to keep track of how many characters have
// currently been added
$i = 0;

// Add random characters from $possible_chars to $unique_ref
// until $unique_ref_length is reached
while ($i < $unique_ref_length) {

// Pick a random character from the $possible_chars list
$char = substr($possible_chars, mt_rand(0, strlen($possible_chars)-1), 1);

$unique_ref .= $char;

$i++;

}

// Our new unique reference number is generated.
// Lets check if it exists or not
$query = "SELECT group_id FROM messages
WHERE group_id='".$unique_ref."'";
$result = mysql_query($query) or die(mysql_error().' '.$query);
if (mysql_num_rows($result) == 0) {

// We've found a unique number. Lets set the $unique_ref_found
// variable to true and exit the while loop
$unique_ref_found = true;
$group_id = $unique_ref;
}

}





}
}
// ends overall checking for message group id

The above -- There might be a better way to start this out...but I tried a union and it wouldnt work for some reason. But basically,

It searches database for group id where to=$to and from=$from , if found it sets group id if not found it searches where to=$from and from=$to (opposite), if found it sets group id, if not found it creates a new unique id using php script.

It's not working -- this keeps want to go back to the following numbers -- 2147483647 --- any idea where its failing? And inputs/examples of how i can improve this are greatly appreciated! I'm just trying to assign message group id's to combine messages between the same people.

[kdjernigan]

I think my issue with the union was there two WHERE clauses..not sure if UNION or UNION ALL can have multiple where clauses..it kept throwing an error until i took it out.

Also, the $cgidcount++ I altered from the $count++ i found online -- not sure if I can do this?

[rocknbil]

Welcome aboard kdjernigan, first let's cut down some of the uneccessary queries, beginning with these two:

$checkgroupid = mysql_query("SELECT group_id FROM messages WHERE to_user = '$to' and from_user = '$from' ORDER BY group_id LIMIT 1");

$cgidcount = "0";
while ($line = mysql_fetch_assoc($checkgroupid)) {
$cgidcount++;
}

if ($cgidcount == "1") {
$cgid1 = mysql_query("SELECT group_id FROM messages WHERE to_user = '$to' and from_user = '$from' LIMIT 1");
while($row = mysql_fetch_array($cgid1)) {
$group_id = $row['group_id'];
}
}


You select, get a count (and are limiting it to 1, so it can ONLY BE 1 or 0,) then select again to get the group id. That can be one query. Rather than structuring your entire logic around $cgidcount, just structure it around whether $group_id is null or not. You start off null, if found, you populate it.

$group_id=null; // initialize
$query = "select group_id FROM messages where to_user = '$to' and from_user = '$from' order by group_id LIMIT 1"
$result = mysql_query($query) or die("Cannot check for group id");
// While = multiple records, only need "if" if there's one
if ($row = mysql_fetch_array($result)) { $group_id = $row['group_id']; }

now instead of

if ($cgidcount == "0") {

you can do

if (! $group_id) {

Now let's look at your random numbers. You're treating it as a string, not a set of digits, which is a really odd approach. :-) Is there a reason you don't just use mt_rand directly on numbers? Looks to me like your group_id field is numeric (and it should be.)

$test_num = mt_rand(10000000, 9999999999);

That will give you a random number between 8 digits and 10 digits.

All together, give this a shot. It's typed on the fly so it may need some debugging.

$group_id=null; // initialize 
$unique_ref_found=null; 
$startnum = 10000000; 
$endnum = 9999999999; 
$testnum = 0; 
// 
$query = "select group_id FROM messages where to_user = '$to' and from_user = '$from' order by group_id limit 1"; 
$result = mysql_query($query) or die("Cannot check for group id " . mysql_error()); 
// While = multiple records, only need "if" if there's one expected 
if ($row = mysql_fetch_array($result)) { $group_id = $row['group_id']; } 
// 
if (! $group_id) { 
 while (! $group_id) { 
 $testnum = mt_rand($startnum, $endnum); 
 // Numeric queries do NOT need quoting 
 $query = "select group_id from messages where group_id=$testnum"; 
 $result = mysql_query($query) or die(" error at checking testnum mysql_error().' '.$query); 
 if (mysql_num_rows($result) == 0) { $group_id=$testnum; } 
 } // end while 
} // end no group id 
// 
// echo "Group id: $group_id"; 

Edit: A while condition based on something working properly is always dangerous. If $group_id never gets populated, your while loop will run forever. Note the added $max_loop and $cnt variables and what they do.

$group_id=null; // initialize 
$unique_ref_found=null; 
$startnum = 10000000; 
$endnum = 9999999999; 
$testnum = $cnt = 0; 
$max_loop=1000; 
// 
$query = "select group_id FROM messages where to_user = '$to' and from_user = '$from' order by group_id limit 1"; 
$result = mysql_query($query) or die("Cannot check for group id " . mysql_error()); 
// While = multiple records, only need "if" if there's one expected 
if ($row = mysql_fetch_array($result)) { $group_id = $row['group_id']; } 
// 
if (! $group_id) { 
 while (! $group_id) { 
 $cnt++; 
 if ($cnt > $max_loop) { break; } // stop me from infinite loop 
 $testnum = mt_rand($startnum, $endnum); 
 // Numeric queries do NOT need quoting 
 $query = "select group_id from messages where group_id=$testnum"; 
 $result = mysql_query($query) or die(" error at checking testnum mysql_error().' '.$query); 
 if (mysql_num_rows($result) == 0) { $group_id=$testnum; } 
 } // end while 
} // end no group id 
// 
// echo "Group id: $group_id"; 

[kdjernigan]

Thanks. I got it temporarily working as of now. But I will definitely try out your code next time I alter it.