mdarling Posted March 23, 2010 Share Posted March 23, 2010 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();} Quote Link to comment https://forums.phpfreaks.com/topic/196279-php-mysql-create-view/ Share on other sites More sharing options...
o3d Posted March 23, 2010 Share Posted March 23, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/196279-php-mysql-create-view/#findComment-1030761 Share on other sites More sharing options...
mdarling Posted March 23, 2010 Author Share Posted March 23, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/196279-php-mysql-create-view/#findComment-1030776 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.