Php & MySQL=> CREATE VIEW

[mdarling]

ok so i cant get the create veiw to work .... it will work in phpmyadmin and with dbforge but not in the site... here is the code

my server version is 5.1.36

the errors i get are: either the table comply is not there  or if i exclude the 2nd select statement i get no database selected  but i can run other query's just fine!... and all permissions are set for the user.

 

 

 

 

$sql1=mysql_query(" CREATE VIEW comply AS

SELECT quiz_compliance.grade, quiz.title as 'quiz', course.title as 'course', quiz_compliance.finish, user.first, user.last, user.id

FROM $dbname.course_enrollments

INNER JOIN $dbname.course ON course_enrollments.course_id = course.id

INNER JOIN $dbname.user ON course_enrollments.user_id = user.id

INNER JOIN $dbname.quiz_compliance

INNER JOIN $dbname.quiz ON quiz.course_id = course.id AND quiz_compliance.quiz_id = quiz.id

WHERE quiz_compliance.user_id = user.id AND course.id = '7'

");

 

$sql4=mysql_query(" SELECT user.id, user.`first`, user.`last`

FROM emscloud.user

LEFT OUTER JOIN emscloud.comply ON user.id = emscloud.comply.id

WHERE emscloud.comply.id IS NULL");

 

echo "\n\n";

echo "User ID,Last,First,\n\n";

if ($sql1){

while($s=mysql_fetch_array($sql4)){

$user_id = $s["id"];

$Last_name = $s["last"];

$first_name = $s["first"];

 

 

echo $user_id.",".$Last_name.",".$first_name;

echo "\n";

 

}

}else{ print mysql_error();}

 

[zeodragonzord]

Try selecting the database first and see if that helps.

 

USE databasename;

 

When you first connect to your database, you should select the database name as well.  Make sure it does that already; if so you don't need to use the above code.

 

Also, check to make sure the View is not already there, if you're testing it, you may have created this table already.

[Mchl]

mysql_select_db