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();}

[o3d]

It might be that the view already exist, so try to create or replace the view. I created a simple example (tested in mysql but not in php):

 

$sql1 = mysql_query("	CREATE or REPLACE VIEW ztest AS
										SELECT 'john' as name UNION
										SELECT 'peter' as name") or die(mysql_error());

$sql2 = mysql_query("	SELECT a.name, b.name
										FROM ztest a JOIN (
											SELECT 'quinn' as name UNION
											SELECT 'peter' as name
										) b ON b.name = a.name") or die(mysql_error());

//then mysql_fetch_array on sql2

[mdarling]

no  the  view doesn't exist and even when i change the code it still doesn't  create the view.  as i said before i works properly but the moment i try to do it in the web page itself it doesn't work. i am extremely  competent when it comes to php/mysql coding. the whole site revolves around it. but the moment i introduced this into the code it chooses not to recognize the create or something.