[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

I need help with a random array selection using MYSQL and PHP

I am trying to build an app in PHP that will select a user (tutor), select a group of students that are not assigned a tutor (based on a counter), and then select one of those students at random and assign them to the user (tutor) for a set amount of time (currently 1 year). Something in this program is going wrong and I cannot seem to pin it down. I have the nagging feeling that it is something simple and I am just missing it.

When run against the database the inital query only returns one value. There are currently 154 student records, all with a students_counts value of 0. I have placed T/S blocks into the code:  The T/S blocks produce the following results against the database:
STUDENT_RESULT VALUES
Key 0, Value 6
Key students_id, Value 6

RANDOM_STUDENT VALUE
6

STUDENT_RESULT2 VALUES
Key 0, Value 6
Key officers_id, Value 6

I have included a table structure dump and hopefully have added enough comments to the code so that my purpose is clear. Any help would be appreciated. If further information is desired please let me know.
<?PHP
//retrieve stored database connection values
require("common.php"); 
 
//connect to the database
if ($user != "" and $password != "") {
	//set up SQL connection
	$link = mysql_connect ($server, $user, $password);
	
	if (! $link) {
		die ("Couldn't connect to mySQL server");
	}
	
	if (!mysql_select_db ($db, $link) ) {
		die ("Couldn't open $db: ".mysql_error() );
	}
}
      
//retrieve the user id passed in as the variable named "cust_id"
$find_user_id = $_GET['cust_id'];
 
//look up the user record to find a tutor
 
$user_query = "SELECT user_id, user_name FROM users WHERE user_id = '$find_user_id' AND user_active = '1' ";
$user_result = mysql_query("$user_query");
$user_result = mysql_fetch_array($user_result);	
$user_id = $user_result['user_id'];  
$user_name = $user_result['user_name'];     	
 
// Find all students without an assignment; if all students have an assignment then
// find all students having only one assignment; if all students have more than one
// assignment keep looping until you get one or more values
 
$count_number = 0;
$valid_result = FALSE;
 
while ($valid_result == FALSE) {
    	$student_query = "SELECT students_id FROM students WHERE students_counts = '$count_number' ";
    	$student_result = mysql_query("$student_query"); 
		if($student_result) {
		   break;
		}   
		$count_number++;
}
$student_result = mysql_fetch_array($student_result);
 
// TROUBLESHOOTING CODE BLOCK - REPORT BACK ON ARRAY VALUES
echo 'STUDENT_RESULT VALUES<BR>';
foreach($STUDENT_result as $key => $value) {		
    echo "Key $key, Value $value<BR>";
}
 
// select one student record at random from the results array and retrieve the pertinent
// information
 
$random_student = $student_result[array_rand($student_result)];
 
 
// TROUBLESHOOTING CODE BLOCK - REPORT BACK ON ARRAY VALUES
echo 'RANDOM_STUDENT VALUE<BR>';	
echo $random_student, '<BR><BR>';
 
// this information is used for the assignment search
$students_id = $random_student['students_id'];
 
// look up the full information on this student
$student_query2 = "SELECT * FROM students WHERE students_id = '$students_id' ";
$student_result2 = mysql_query("$student_query");
$student_result2 = mysql_fetch_array($student_result2);
 
// TROUBLESHOOTING CODE BLOCK - REPORT BACK ON ARRAY VALUES
echo 'STUDENT_RESULT2 VALUES<BR>';
foreach($student_result2 as $key => $value ) {		
    echo "Key $key, Value $value<BR>";
}
 
// this information is used for the assignment record
$students_id = $student_result2['students_id'];
 
// this information is used for display only
$students_lname= $student_result2['students_lname'];
$students_title= $student_result2['students_title'];
$students_group= $student_result2['students_group'];
 
// set the expiration date based on the 12/31 at the end of the current year
$expires = mktime(0,0,0,12,31,date("Y"));
 
// add the assignment record to the assignment table
$assignment_query = "INSERT INTO assignments (user_id, students_id, assignment_expired) VALUES ('$user_id','$students_id','$expires')";
mysql_query("$assignment_query");
 
// update the students record to show the new assignment count
$students_counts++;
$student_query3 = "UPDATE students SET students_counts = '$students_counts' WHERE students_id = '$students_id' ";
mysql_query("$students_query3");
 
?>
DATABASE STRUCTURE DUMP:
 
-- phpMyAdmin SQL Dump
-- version 2.11.9.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 23, 2009 at 11:37 AM
-- Server version: 5.0.67
-- PHP Version: 5.2.4
 
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
 
--
-- Database: `jo151`
--
 
-- --------------------------------------------------------
 
--
-- Table structure for table `assignments`
--
 
CREATE TABLE IF NOT EXISTS `assignments` (
  `user_id` int(11) NOT NULL,
  `students_id` int(4) NOT NULL,
  `assignment_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `assignment_expired` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`user_id`,`students_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
-- --------------------------------------------------------
 
--
-- Table structure for table `students`
--
 
CREATE TABLE IF NOT EXISTS `students` (
  `students_id` int(4) NOT NULL auto_increment,
  `students_fname` varchar(30) collate utf8_unicode_ci NOT NULL,
  `students_lname` varchar(30) collate utf8_unicode_ci NOT NULL,
  `students_title` varchar(30) collate utf8_unicode_ci NOT NULL,
  `students_group` varchar(50) collate utf8_unicode_ci NOT NULL,
  `students_counts` int(2) NOT NULL,
  PRIMARY KEY  (`students_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=158 ;
 
-- --------------------------------------------------------
 
--
-- Table structure for table `users`
--
 
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL auto_increment,
  `user_username` varchar(30) collate utf8_unicode_ci NOT NULL,
  `user_password` varchar(15) collate utf8_unicode_ci NOT NULL,
  `user_question` varchar(255) collate utf8_unicode_ci NOT NULL,
  `user_answer` varchar(255) collate utf8_unicode_ci NOT NULL,
  `user_name` varchar(50) collate utf8_unicode_ci NOT NULL,
  `user_address` varchar(30) collate utf8_unicode_ci NOT NULL,
  `user_city` varchar(30) collate utf8_unicode_ci NOT NULL,
  `user_state` varchar(4) collate utf8_unicode_ci NOT NULL,
  `user_zip` varchar(10) collate utf8_unicode_ci NOT NULL,
  `user_email` varchar(50) collate utf8_unicode_ci NOT NULL,
  `user_phone` varchar(15) collate utf8_unicode_ci NOT NULL,
  `user_startdate` datetime NOT NULL,
  `user_expire` date NOT NULL,
  `user_active` varchar(4) collate utf8_unicode_ci NOT NULL,
  `user_paid` varchar(11) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16 ;

Open in new window

0
ddcsltd
Asked:
ddcsltd
  • 2
  • 2
1 Solution
 
Michael701Commented:
this

"SELECT students_id FROM students WHERE students_counts = '$count_number' ";

probably looks like

SELECT students_id FROM students WHERE students_counts = ''

not what you'd like, so change it to this

"SELECT students_id FROM students WHERE students_counts = '".(int) $count_number."' ";

0
 
Michael701Commented:
Next would be you're misunderstanding of the mysql_fetch_array

$student_result = mysql_fetch_array($student_result);

this does NOT fetch all results and place then in an array. it only fetches ONE student record.

You'll want something like

mysql_data_seek($student_result, rand(0,mysql_num_rows($student_result)-1));
$random_student=mysql_fetch_assoc($student_result);
0
 
ddcsltdAuthor Commented:
Thanks for the help; it has been a long time since I have needed to retrieve a complete data set using PHP. Sad thing is that I knew this once upon a time.
0
 
ddcsltdAuthor Commented:
Thanks again for the fast response; I do appreciate it and so will the kids.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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