latemodern

Hi There, I am trying to use a temporary table but it doesn't appear to be working. //create the temporary table $temp = "CREATE TEMPORARY TABLE temp ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id SMALLINT UNSIGNED NOT NULL, name VARCHAR(50) NOT NULL, gender VARCHAR (10) NOT NULL, photo_file VARCHAR(30), minbeds SMALLINT(6) NOT NULL, maxbeds SMALLINT(6) NOT NULL, minrent SMALLINT(6) NOT NULL, maxrent SMALLINT(6) NOT NULL, smoker VARCHAR(20), smokingmix VARCHAR(20), studentmix VARCHAR(20), minage SMALLINT(6) NOT NULL, maxage SMALLINT(6) NOT NULL, email VARCHAR(50), phone VARCHAR(20), description TEXT, stu VARCHAR(20), gend VARCHAR(20), comp_score SMALLINT, PRIMARY KEY (id) )"; if($tempresult = mysql_query($temp)){ echo 'Result'; }else{ echo mysql_error(). $temp; } //Insert temporary values $temp_insert = "INSERT INTO temp (user_id, name, age, gender, photo_file, minbeds, maxbeds, minrent, maxrent, smoker, smokingmix, studentmix, minage, maxage, email, phone, description, stu, gend, comp_score) VALUES ('$user_id', '$name', $age, '$gender', '$photo_file', $minbeds, $maxbeds, $minrent, $maxrent, '$smoker', '$smokingmix', '$studentmix', $minage, $maxage, '$email', '$phone', '$description', '$stu', '$gend', $comp_score)"; //not sure if i need this bit $temp_insert_result = mysql_query($temp_insert); //then test whether there was a result $megaquery = "SELECT name FROM temp"; if($megaresult = mysql_query($megaquery)){ echo $megaquery; while($megarow = mysql_fetch_array($megaresult, MYSQL_ASSOC)){ echo '<p>' .$megarow['name']. '</p>'; } } else { echo mysql_error(); echo $megaquery; } I get the following text on the page (as you would expect) but nothing else. Have the queries worked? I was expecting to get a list of names. The variable values that I am trying to insert into the table aren't empty and they appear if I echo them before I try to insert them in the temporary tabel Am I doing something wrong? Thanks a lot Chris

Hi There, I am trying to construct a MYSQL query to filter out people who are too old or too young based on their date of birth and the users $maxage and $minage specifications I created an equation that takes that date if birth (from the users.dob category) and calculates their age and aliases it as 'age'. In my WHERE clause I am trying to get it to only include users whose age is higher than the $minage variable and lower than the $maxge variable. However, mysql returns an error saying that 'age' is an unknown category. Any ideas? Here is the code: //cut out people who are too young $filter = $filter. ' age >=' .$own_minage. ' AND '; //cut out people who are too old $filter = $filter. ' age <=' .$own_maxage. ' AND '; $user_record_query = "SELECT users.user_id, CONCAT(first_name, ' ', last_name) AS name, (YEAR(CURDATE())-YEAR(dob)) - (RIGHT(CURDATE(),5)<RIGHT(dob,5)) AS age, gender.gender, minbeds, maxbeds, minrent, maxrent, minage, maxage, email, phone, personal_description, gendermix.gendermix, file_name, smoker.smoker, studentmix.studentmix, smoking_mix.smokingmix, student.student FROM users JOIN gender ON users.gender = gender.gender_id JOIN uploads ON uploads.user_id= users.user_id JOIN smoker ON users.smoker=smoker_id JOIN studentmix ON users.studentmix_id=studentmix.studentmix_id JOIN smoking_mix ON users.smokingmix_id=smoking_mix.smokingmix_id JOIN users_area_assoc ON users_area_assoc.user_id = users.user_id JOIN area ON area.area_id=users_area_assoc.area_id JOIN student ON users.occupation=student.student_id JOIN gendermix ON users.gendermix_id=gendermix.gendermix_id WHERE $filter" Thanks Chris