mysql - How to use multiple queries in one php function -
hi wondering how php function run 2 sql queries function @ moment code below connection database @ top of page don't need right connection every time full page code :
<?php class selectlist { protected $conn; public function __construct() { $this->dbconnect(); } protected function dbconnect() { include "db_config.php"; $this->conn = mysql_connect($host,$user,$password) or die("unable connect database"); mysql_select_db($db,$this->conn) or die("can not select database $db"); return true; } public function showcategory() { $sql = "select subject.subject_id,subject.description,count(media.subject_id) media_count\n" . "from subject left outer join media on subject.subject_id = media.subject_id \n" . "where verified = 1\n" . "group subject.subject_id,subject.description order subject.description "; $res = mysql_query($sql,$this->conn); $category = '<option value="%">subject</option>'; while($row = mysql_fetch_array($res)) { $category .= '<option value="' . $row['subject_id'] . '">' . $row['description']. '...('.$row['media_count'].')</option>'; } return $category; } public function showtype() { $sql = "select section.section_id,section.description,section.subject_id,count(media.section_id) media_count section left outer join media on section.section_id = media.section_id , section.subject_id = media.subject_id section.subject_id={$_post['id']} , verified = 1 group section.section_id,section.description"; $res = mysql_query($sql,$this->conn); $type = '<option value="%">choose...'; while($row = mysql_fetch_array($res)) { $type .= '<option value="' . $row['section_id'] . '">' . $row['description']. '...('.$row['media_count'].') </option>'; } return $type; } public function showprinciple() { $sql = "select principle.principle_id,principle.description,principle.section_id,count(media.principle_id) media_count principle left outer join media on principle.principle_id = media.principle_id , principle.section_id = media.section_id principle.section_id={$_post['id']} , verified = 1 group principle.principle_id,principle.description"; $res = mysql_query($sql,$this->conn); $principle = '<option value="%">choose...</option>'; while($row = mysql_fetch_array($res)) { $principle .= '<option value="' . $row['principle_id'] . '">' . $row['description']. '...('.$row['media_count'].') </option>'; } return $principle; } public function getresults() { //$vars = $_post["comboboxselections"]; // gets comboboxselections index.php list($subjectid, $sectionid, $principleid) = explode('#', $_post["comboboxselections"]); $box = ""; // placeholder html results returned $sql = "select media_id,title,blurb media subject_id = $subjectid , section_id = $sectionid , principle_id= $principleid , verified = 1"; // sql query $hasprintedrecords = false; // capture records if there no records printed screen $res = mysql_query($sql,$this->conn); // connection database , getting results query while($row = mysql_fetch_array($res)) // loop while there results within array run function { $hasprintedrecords = true;// if there records equals true if not equals false $box .= ' <div style="margin-top:5px;height:120px;padding:0px;"id="video"> <div style="width:185px;height:105px;float:left;" id="videoimage"> <img style="width:185px;"class="partimg1" src="images/thumbnails/'.$row['media_id'].'.png" /> </div> <div style="float:right;padding-left:5px;width:72%;font-size:15px;padding-top:0px;font:verdana,sans-serif;" id="text"> <div style="color:#0099cc;font-weight:bold;" id="title">'.$row['title'].'</div> <a style="color:#000000;text-decoration:none;padding:0px;margin:0px;font-size:12px;" href="http://www.thomasdudley.co.uk/" target="_blank">by thomas dudley</a> <div style="font-size:13.4px;"id="blurb">'.$row['blurb'].'</div> <div id="downloads"> <a href="http://thor/classroom/downloads/'.$row['media_id'].''.$row['title'].'.exe" target="_blank"> <img style="width:100px; margin-left:-10px;margin-top:10px;" src="images/download.jpg"> </a> </div> </div> </div>'; // if results available display screen } if ( $hasprintedrecords == false) // if there no results function below run { $box .='<div id=\"video\" style=\" border-style:solid; border-color:#000000; border-width:1px;\"> <div style="border-style:dashed; border-color:#f90;" id="text"> no media found selection.</div></div>'; // returns html message screen } return $box; // returns results screen } public function showjobrole() { $sql = "select jobrole_id,description jobrole "; $res = mysql_query($sql,$this->conn); $jobrole = '<option value="%">job role</option>'; while($row = mysql_fetch_array($res)) { $jobrole .= '<option value="' . $row['jobrole_id'] . '">' . $row['description'] . '...('.$row['media_count'].'</option>'; } return $jobrole; } public function showcareer() { $sql = "select career_id,description career jobrole_id={$_post['id']}"; $res = mysql_query($sql,$this->conn); $career = '<option value="%">career</option>'; while($row = mysql_fetch_array($res)) { $career .= '<option value="' . $row['career_id'] . '">' . $row['description'] . '</option>'; } return $career; } public function get_job () { //$vars1 = $_post["comboboxselections1"]; // gets comboboxselections index.php list($jobroleid, $careerid) = explode('#', $_post["comboboxselections1"]); $res = mysql_query($sql,$this->conn); $box = ""; // placeholder html results returned $sql = "select title,blurb,m.media_id media_career_crossref mcc inner join media m on m.media_id = mcc.media_id mcc.jobrole_id = $jobroleid , mcc.career_id = $careerid"; $hasprintedrecords = false; // capture records if there no records printed screen $res = mysql_query($sql,$this->conn); // connection database , getting results query while($row = mysql_fetch_array($res)) // loop while there results within array run function { $hasprintedrecords = true;// if there records equals true if not equals false $box .= ' <div style="margin-top:5px;height:120px;padding:0px;"id="video"> <div style="width:185px;height:105px;float:left;" id="videoimage"> <img style="width:185px;"class="partimg1" src="images/thumbnails/'.$row['media_id'].'.png" /> </div> <div style="float:right;padding-left:5px;width:72%;font-size:15px;padding-top:0px;font:verdana,sans-serif;" id="text"> <div style="color:#0099cc;font-weight:bold;" id="title">'.$row['title'].'</div> <a style="color:#000000;text-decoration:none;padding:0px;margin:0px;font-size:12px;" href="http://www.thomasdudley.co.uk/" target="_blank">by thomas dudley</a> <div style="font-size:13.4px;"id="blurb">'.$row['blurb'].'</div> <div id="downloads"> <a style="width:100px; margin-left:-10px;margin-top:10px;" href="http://thor/classroom/downloads/'.$row['media_id'].''.$row['title'].'.exe" target="_blank"> <img style="width:110px;" src="images/download.jpg"> </a> </div> </div> </div>'; // if results available display screen } if ( $hasprintedrecords == false) // if there no results function below run { $box .='<div id=\"video\" style=\" border-style:solid; border-color:#000000; border-width:1px;\"> <div style="border-style:dashed; border-color:#f90;" id="text"> no media found selection.</div></div>'; // returns html message screen } return $box; // returns results screen } public function unverified() { $unverified = ""; $sql = "select * media verified = 0 "; // sql query $box = ""; $hasprintedrecords = false; // capture records if there no records printed screen $res = mysql_query($sql,$this->conn); // connection database , getting results query while($row = mysql_fetch_array($res)) // loop while there results within array run function { $hasprintedrecords = true;// if there records equals true if not equals false $unverified .= ' <div style="margin-top:5px;height:120px;padding:0px;"id="video"> <div style="width:185px;height:105px;float:left;" id="videoimage"> <img style="width:185px;"class="partimg1" src="images/thumbnails/'.$row['media_id'].'.png" /> </div> <div style="float:right;padding-left:5px;width:72%;font-size:12px;padding-top:0px;font:verdana,sans-serif;" id="text"> <div style="color:#0099cc;font-weight:bold;" id="title">'.$row['title'].'</div> <a style="color:#000000;text-decoration:none;padding:0px;margin:0px;font-size:12px;" href="http://www.thomasdudley.co.uk/" target="_blank">by thomas dudley</a> <div style="font-size:13.4px;"id="blurb">'.$row['blurb'].'</div> <div id="downloads"> <a id="download" href="http://thor/classroom/downloads/'.$row['media_id'].''.$row['title'].'.exe" target="_blank">download</a> </div> </div> </div>'; // if results available display screen } if ( $hasprintedrecords == false) // if there no results function below run { $unverified .='<div id=\"video\" style=\" border-style:solid; border-color:#000000; border-width:1px;\"> <div style="border-style:dashed; border-color:#f90;" id="text"> no media found selection.</div></div>'; // returns html message screen } return $unverified; // returns results screen } public function addnewmediarecord($subject, $section, $principle, $title, $blurb, $uniq_id) { $sql = "insert media (media_id, subject_id, section_id, principle_id, title, blurb, verified, media_uniqid) values ('null', '".$subject."', '".$section."', '".$principle."', '".$title."', '".$blurb."', '0', '".$uniq_id."')"; $this->addmediacrossref($job, $career, $uniq_id); // don't have vars initilized in current method? mysql_query($sql, $this->conn); (return mysql_affected_rows($this->conn) > 0); } public function addmediacrossref($job, $career, $uniq_id) { $sql = "insert media_career_crossref (media_id, jobrole_id, career_id, verified, media_uniqid) values (null, '".$job."', '".$career."', '0', '".$uniq_id."')"; mysql_query($sql, $this->conn); (return mysql_affected_rows($this->conn) > 0); } } $opt = new selectlist(); ?> above full code have got web page latest functions last on page.
this how functions being called different page
if (move_uploaded_file($_files['userfile']['tmp_name'], $uploadfile)) { echo "file valid, , uploaded.\n"; include "select.class.php"; /*$opt->addnewmediacrossref($job, $career, $uniq_id);*\ /* me trying call both functions @ once didnt work either*/ $opt->addnewrecord($subject, $section, $principle, $title, $blurb, $uniq_id); } any appreciated
the other answer might have stated can use mysqli's multiquery option, , commented can use *_query() once first statement , second time second statement. however, braking single responsibility principle of method. if want chain actions within one, separate them in different methods , call inwithin.
public function addnewmediarecord($subject, $section, $principle, $title, $blurb, $uniq_id) { $sql = "insert media (media_id, subject_id, section_id, principle_id, title, blurb, verified, media_uniqid) values ('null', '".$subject."', '".$section."', '".$principle."', '".$title."', '".$blurb."', '0', '".$uniq_id."')"; $this->addmediacrossref($job, $career, $uniq_id); // don't have vars initilized in current method? return yourqueryfunction($sql, $this->conn); } public function addmediacrossref($job, $career, $uniq_id) { $sql = "insert media_career_crossref (media_id, jobrole_id, career_id, verified, media_uniqid) values (null, '".$job."', '".$career."', '0', '".$uniq_id."')"; return yourqueryfunction($sql, $this->conn); } and methods not return query() return value, when insert/update
i prefer:
public function addnewmediarecord($all_the_params) { $sql = "....."; yourqueryfunction($sql); $this->chainedmethod($params); return yourfunctionforaffected_rows() > 0; } so return boolean if affected rows more 0 (successful insert).
also you'd better wrap database functions in order have easier switch between libraries, when need to.
e.g.:
class database { private $_host; private $_user; private $_pass; private $_db; private $_conn; public function __construct($host, $user, $pass, $db) { $this->_host = $host; $this->_user = $user; $this->_pass = $pass; $this->_db = $db; $this->connect(); } private function connect() { $this->_conn = mysqli_connect($this->_host, $this->_user, $this->_pass, $this->_db) or die(mysqli_error($this->_conn)); } public function query($query) { return mysqli_query($this->_conn, $query); } public function affectedrows() { return mysqli_affected_rows($this->_conn); } class theclasswhereyourmethodsyouhaveshownare { protected $_db; public function __construct() { $this->_db = new database('host', 'user', 'pass', 'db'); } public function addnewmediarecord($subject, $section, $principle, $title, $blurb, $uniq_id) { $sql = "insert media (media_id, subject_id, section_id, principle_id, title, blurb, verified, media_uniqid) values ('null', '".$subject."', '".$section."', '".$principle."', '".$title."', '".$blurb."', '0', '".$uniq_id."')"; $this->addmediacrossref($job, $career, $uniq_id); // don't have vars initilized in current method? $this->_db->query($sql); return $this->_db->affectedrows > 0; } 
Comments
Post a Comment