Search the Community

Hey all, I'm just starting to get on the PDO bandwagon with PHP. One thing I do a lot is to fetch the result of a query straight into a variable using list. Something like list($name) = mysql_fetch_row(mysql_query("SELECT name FROM names WHERE userID='1'")); Is there a similar thing I can do with PDO rather than having to loop through with a while or similar. Thanks in advance.

This is mysql code im trying to run: SELECT i.id, i.courseid, i.title, i.info, i.lasteditedby, u.id, u.forename, u.surname FROM courseinformation as i JOIN users AS u ON (i.lasteditedby = u.id) WHERE i.courseid = :courseid ORDER BY i.id desc LIMIT 2; Im getting this error : /* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':courseid ORDER BY i.id desc LIMIT 2' at line 1 */ My aim is to get id, courseid, title, info, lasteditedby from CourseInformation table, and then Id, forename and surname from user table. Where the userid is the same as lasteditedby. I really can't see what sql syntax is wrong as i've used :courseid in other pdo sql querys that ive run for reference, this is my php code with that sql in $courseid = 'G11111'; $sql = "SELECT i.id, i.courseid, i.title, i.info, i.lasteditedby, u.id, u.forename, u.surname FROM courseinformation as i JOIN users AS u ON (i.lasteditedby = u.id) WHERE i.courseid = :courseid ORDER BY i.id desc LIMIT 2"; $sql->bindParam(":courseid", $courseid); $sql->execute(); foreach ($db->query($sql) as $row) { echo '<div class="announceTitle">'; echo $row['title'].'<br />'; echo $row['forename'].' '.$row['surname'].'<br />'; echo '</div> <div class="announceText">'; echo $row['info']; echo '</div> <br /> <br />'; } Could anyone please point me in the direction as to what is wrong? Thanks for reading

this is the code with bind_result: function getUserDetails($username=NULL, $id=NULL) { if($username!=NULL) { $column = "user_name"; $data = $username; } elseif($id!=NULL) { $column = "id"; $data = $id; } global $db; $query = $db->prepare("SELECT id, username, permissions, forename, surname, password, email, courseid, choiceid, lastlogin, active FROM users WHERE $column = :column"); $query->bindParam(":column", $data); $query->execute(); $query->bind_result ($id, $username, $permissions, $forename, $surname, $password, $email, $courseid, $choiceid, $lastlogin, $active); while ($query->fetch()){ $row = array('id' => $id, 'userlevel' => $permissions, 'username' => $username, 'forename' => $forename, 'surname' => $surname, 'password' => $password, 'email' => $email, 'courseId' => $courseid, 'choiceId' => $choiceId, 'lastlogin' => $lastlogin, 'active'=> $active); } return ($row); } I've been trying to convert this to work with PDO, as I've found out that PDO doesnt support bind_result. I've read arround about using fetch assoc but im not entirely sure how to implement it I've tried this: function getUserDetails($username=NULL,$id=NULL) { if($username!=NULL) { $column = "user_name"; $data = $username; } elseif($id!=NULL) { $column = "id"; $data = $id; } global $db; $query = $db->prepare("SELECT id, username, permissions, forename, surname, password, email, courseid, choiceid, lastlogin, active FROM users WHERE $column = :column"); $query->bindParam(":column", $data); $query->execute(); $results = array(); while ($row = $query->fetch(PDO::FETCH_ASSOC)) { $results[] = $row; } return ($results); } This is a sample of how im trying to use the code $username = '123'; $userdetails = getUserDetails($username); echo $userdetails['surname']; Could anyone please give me a poke in the direction I should be going? I have searched around but I'm just getting more confused.

[edit] i believe ive posted this in the wrong section D: please could a mod delete or move it! thanks! ----------------------- Hi Im trying to get : - title, newstext, authorId from indexNews - id, forename, surname from users where authorId is id im using pdo, which is where im stuck here is what I've done so far : try { $sql = "SELECT i.title, i.newstext, i.authorId, u.id, u.forename, u.surname FROM indexNews as i JOIN users AS u ON (i.authorId = u.id)"; //$sql = "SELECT * FROM indexNews"; foreach ($db->query($sql) as $row) { echo '<div class="announceTitle">'; echo $row['i.title'] .' - '. $row['i.authorId'] . '<br />'; echo $row['u.surname'].' '.$row['u.forename'].'<br />'; echo '</div> <div class="announceText">'; echo $row['i.newstext']; echo '</div> <br /> <br />'; } } catch(PDOException $e) { echo $e->getMessage(); } when i run this i get : line 100 is echo $row['i.title'] .' - '. $row['i.authorId'] . '<br />'; i think that the issue might be with the array? but i'm not sure. I've tried googling and have been looking for a tutorial, but haven't found an answer. could anyone give me some hints as to where i'm going wrong? Thanks for your time

Hey I have a simple articles table with IDs. I want to get the highest and lowest ids from the latest 10 results. For example, if there are 11 ids, the result should be 2 and 11 and if there are 4 ids, should be 4 and 1 and so on. I use PHP PDO statements. $aid = $DBH->prepare("SELECT id FROM articles ORDER BY id DESC LIMIT 10"); $aid->execute(); $row = $aid->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_LAST); $lowest_article_id = $row[0]; $row = $aid->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_FIRST); $highest_article_id = $row[0]; The above will always return 11 if there are 11 records and 10 if there are 10 records. So, the query ignores the PDO::FETCH_ORI_LAST and PDO::FETCH_ORI_FIRST part of the query. Thanks

How can I tell if a given installation has PDO installed? I'm using a hosting service and they have PHP 5.3.19 but I suspect they don't have PDO. I'm just not sure how to tell if it is there. I'm new to php but not to database work. I was trying to perfect some mysql_query code when I stumbled on the existence of PDO, which is apparently the preferred method of doing database access these days. I had a look at the PDO tutorial and found it very familiar. I reworked one of my programs to use PDO but now that I'm executing it for the first time, I'm getting an error message that makes me think PDO isn't installed because it doesn't comprehend the -> operator. Here's the error message: Parse error: syntax error, unexpected '>' in /home/foo/public_html/topic_proposal_theme.php on line 106 And here's the statement that is executing: $stmt->execute(array(':date_proposed' ==> $date_proposed, ':proposer' ==> $proposer, ':title' ==> $title, ':discuss' ==> $discuss, ':prepare' ==> $prepare, ':comments' ==> $comments)); The funny thing is that this isn't the very first PDO statement or use of the -> operator in the progam. There are other statements in an include that gets the database connection; that include is situated several lines before the code that is failing. If PDO really wasn't there, I'd expect to see this error either the first time it encountered the -> operator or every time. So maybe I'm completely wrong in assuming PDO isn't there. Maybe there is something else wrong. But I've imitated the PDO tutorial examples pretty carefully so I'm not seeing my error if I've made a simple typo.

I'm letting the admin users be able to create tables for polls and this is the query i have: $aquery = $con->prepare("ALTER TABLE `$table` ADD `$field` $enum DEFAULT '$def' NOT NULL"); the $table and $enum fields are both coming from another table so they are safe, but the $def and $field variables are both coming from the user. How can i check to be sure they are safe to use. I've tried this $aquery->bindParam(':field', $field); $aquery->bindParam(':def', $def); but that doesn't work

Sorry if this is not the correct sub-forum, I just went through most sections and didn't really know the best place. I want to learn object oriented php but can't seem to find any resources which are written in good english. I was wondering if someone knows of any reliable resources for learning pdo? I've already looked on php.net but I wanted more of a tutorial based website written for novices. I hope someone can throw me a few links. Kind regards, L2c

Hi There, I'm rather new to php so please bare with me! After many attempts i still have been unable to return success, even existing usernames still come up as free. this is the code i've been attempting to use; <?php }else{ $usr = new Users; $usr->storeFormValues( $_POST ); if( $_POST['password'] == $_POST['confpass'] ) { //passwords do match// } else { echo "Passwords do not match"; exit; } $con = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD ); $con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); $stmt = $con->prepare("SELECT COUNT (*) FROM users WHERE username = :username"); if ($stmt->fetchColumn() > 0) { echo "username is taken"; }else{ echo "username is free"; } } Thanks, I Appreciate it!

i'm building a project for the first time with PDO this is how i configured it index.php <?php require_once (dirname(__FILE__) . '/inc/main.php'); userlogin(); $HTMLOUT = ''; $stmt = $db->query("SELECT row_id, name, mobile FROM location LIMIT 3"); // $stmt->execute(array($id, $name)); $stmt->setFetchMode(PDO::FETCH_OBJ); $db = null; $stmt = null; ?> inc/main.php <?php require_once (dirname(__FILE__) . '/pdo_conn.php'); require_once (dirname(__FILE__) . '/mail/class.Mail.php'); error_reporting(E_ALL); function userlogin() { global $db; unset($GLOBALS["CURUSER"]); $ip = getip(); $nip = ip2long($ip); $id = 0 + get_cookie('uid'); $fetch_user_details = $db->prepare("SELECT * FROM users WHERE user_id = :bitches_id LIMIT 1"); $fetch_user_details->bindParam(':bitches_id', $id, PDO::PARAM_INT); $fetch_user_details->execute(); $fetch_user_details->setFetchMode(PDO::FETCH_OBJ); $row = $fetch_user_details->fetch(); $user_id = $row->user_id; $user_ip = $row->user_last_ip; $update_user_details = $db->prepare("UPDATE users SET user_last_access = UNIX_TIMESTAMP(), user_last_ip = :last_ip WHERE user_id = :u_id LIMIT 1"); $update_user_details->bindParam(':last_ip', $user_ip, PDO::PARAM_STR, 15); $update_user_details->bindParam(':u_id', $user_id, PDO::PARAM_INT); $update_user_details->execute(); $GLOBALS["CURUSER"] = $row; } function is_logged_in() { global $CURUSER; if (!$CURUSER) { header("Location: domain.net/login.php?403"); exit(); } } ?> inc/pdo_conn.php <?php $db = new PDO('mysql:host=localhost;dbname=abc;charset=UTF-8', 'abc', 'xam'); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); ?> until a few days ago it worked, but when i starting to expand my project i started to get this error PHP Fatal error: Call to a member function prepare() on a non-object in /root/inc/main.php" the error reffers to this line $fetch_user_details = $db->prepare("SELECT * FROM users WHERE user_id = :bitches_id LIMIT 1");

Please some1 help me with this code. i've tried soo many times to retrieve the rooms that are not booked. if no rooms have been booked for a specified period match with the user input, then the room is available for booking. and now my PDO Mysql code is spinning in my head... <?php /** * @author S34N * @copyright 2012 */ class Connection { Public function dbConnect() { return new PDO("mysql:host=localhost; dbname=waterfall", "root", "6354"); } } class Date_a { private $db; public function __construct() { $this->db = new Connection(); $this->db = $this->db->dbConnect(); } public function Query($arr, $dep) { try { if (!empty($arr) && !empty($dep)) //Check_In between booked dates Check_out User Prompted arrival date User Departure Date { $st = $this->db->prepare("SELECT `schedule`.`Check_In` , `schedule`.`RoomNo` , `schedule`.`Check_Out` FROM schedule WHERE (Check_In BETWEEN ? AND ? || Check_Out BETWEEN ? AND ?)"); $st->bindParam(1, $arr); $st->bindParam(2, $dep); $st->bindParam(3, $arr); //("SELECT `schedule`.`Check_In` , `schedule`.`RoomNo` , `schedule`.`Check_Out` FROM schedule WHERE ((Check_In >= ?) AND (Check_Out <= ?))"); $st->bindParam(4, $dep); //("SELECT `schedule`.`Check_In` , `schedule`.`RoomNo` , `schedule`.`Check_Out` FROM schedule WHERE ((? NOT BETWEEN Check_In AND Check_Out) AND (? NOT BETWEEN Check_In AND Check_Out))"); $st->execute(); //("SELECT `schedule`.`Check_In` , `schedule`.`RoomNo` , `schedule`.`Check_Out` FROM schedule WHERE ((Check_In BETWEEN ? AND ?) || (Check_Out BETWEEN ? AND ?) || (? BETWEEN Check_In AND Check_Out) || (? BETWEEN Check_In AND Check_Out))"); if($st->rowCount() >= 1) { $num = $st->rowCount(); echo $num; echo "Booked !"; } else { $status = "Available"; $num = 0; echo $status; } } else { echo "Empty input."; } $this->db = null; //Close Database Connection } catch (PDOException $e) { echo $e->getMessage(); //Returns error message. } } } ?>

Hello Masters... Freaks I have a PDO Persistent Connections question. 1. If I connect to mySQL using PDO persistent connections turned as so.... $db = new PDO("mysql:host=localhost";"dbname=$this->dbName"; "$this->mySQLuser", "$this->mySQLpassword",array(PDO::ATTR_PERSISTENT => true,PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION )); 2. Then I rerun this script (I rerun this script because it is a class that is auto loaded). 4. Will PDO reconnect to mySQL? Will PDO reconnect if the connection parameters change? The whole idea is to create db class that connects to mySQL using a user name and password that changes depending on the state of the user (logged in, not activated, not logged in). (Thought this would improve security because of the changing of the mySQL privliges based on user state, but Im a noob so I really dont know if it helps) Here is my full script. <?php class db { private $dbName='vulnVult'; public $tableName='users'; protected $mySQLuser='anon'; private $mySQLpassword="$this->mySQLuser".'password'; private $db; function __construct($PDO) { try { $db = new $PDO("mysql:host=localhost";"dbname=$this->dbName"; "$this->mySQLuser", "$this->mySQLpassword",array(PDO::ATTR_PERSISTENT => true,PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION )); //Following Logic Sets MySQL user type if ($_SESSION['userID']) { $query= 'SELECT `activated` from `users` where'."$SESSION['userID']".'=`userID`'; $stmt = $db->query($query); $row = $stmt->rowCount(); if ($row = 1) { $this->mySQLuser='registered'; } else { $this->mySQLuser='notActivated'; } } else { $this->mySQLuser='anon' } // return a new PDO object with the corrct MySQL user type. Persistent Connection is On. return new $PDO("mysql:host=localhost";"dbname=$this->dbName"; "$this->mySQLuser", "$this->mySQLpassword",array(PDO::ATTR_PERSISTENT => true,PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION )); } catch (PDOException $e) { echo "<br />There was a problem connecting to the database : ".$e->getMessage()."<br />"; die(); } } } $db = new db(PDO); ?>

I am trying to change from using mysql to PDO, I heard it is very secure, but I think in-order to implement PDO, one needs to learn Object Oriented Programming for PHP. Is this true? And, if so Where can I find, the simplest and easiest to understand tutorials on the web for pdo, or oop.

I have a website streaming project going on,but there's one thing that stops me from really starting it "HOW TO ADD FILE PATH INTO A MYSQL DB?" since I know it will slow down the sgbd to directly add the file in a longblob field,I need some help on how to retrieve those file path(manually?) Let's say a user enters 'die hard 4' in the search form,Do I concatenate the user's input to a query like $query = 'SELECT * FROM T_DIRECTORIES WHERE DIRECTORIES LIKE 'user_input%'; Then do 'scandir($query); Since I will name my directories as the files it contains

Hello all, In a column in my table is store an array 58100, 47270, 95437, 52652 which represents in table1 class_id, class_name 58100 JSS 47270 PRY 95437 SSS in table2 subjects, subj_levels English 58100, 47270, 95437 Maths 58100, 47270 Physics 47270, 95437 I have two problems Problem One when i do a select with join, instead of getting JSS, PRY, SSS as result, i am getting only JSS and the other values not showing up. $stmt=$pdo->query(" SELECT t1.subj_name, t1.subj_levels, t2.class_id FROM tbl_school_subjects t1 LEFT JOIN tbl_classes t2 ON t1.subj_levels = t2.class_id "); WHILE($row=$stmt->fetch(PDO::FETCH_ASSOC)){ echo '<tr> <td>'.$row['subj_name'].'</td> <td>'.$row['class_name_small'].'</td> <td>'; } Problem Two when i do a select find_in_set, i get no result. $ids = $_GET['id']; $stmt = $pdo->query(" SELECT * FROM tbl_school_subjects WHERE FIND_IN_SET($ids, subj_levels) > 0 "); what could be the problem? Thanks

hello all. i dont know if to post this here or javascript/ajax section. if its not the right place, please let me know so i can ask in the right place. i am trying out saving to db via modal form. i learned the basics of using ajax to save data with the modal. so far, i can perform a CRUD via modal but the problem i am having is displaying duplicate entry error inside the modal. I have tried so many ways and the closest i come is displaying error only if a field is duplicate cos i use same trigger as my validation error notice. I'd be glad if i am shown the way to get it to display the error or a better way of getting it done. PS: I want the errors displayed inside the modal. I want the database query error to display where the success message is displayed (i.e on the modalMessage div) Thanks My Modal <div class="modal fade" id="armsModal" data-bs-backdrop="static" tabindex="-1" aria-hidden="true"> <div class="modal-dialog modal-dialog-centered" role="document"> <div class="modal-content"> <div class="modal-header modal-bg"> <h5 class="modal-title w-100 text-center mb-3" id="exampleModalLabel4">Add School Arms</h5> <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button> </div> <form id="submitForm" class="myForm"> <div class="modal-body"> <div id="modalMessage"></div> <div class="mb-3"> <label for="armsname" class="form-label">Arms FullName:</label> <input type="text" class="form-control" id="arms_long_name" name="arms_long_name" autocomplete="off" value="<?php if(isset($_POST['arms_long_name'])){ echo $_POST['arms_long_name']; } ?>"> <span id="longNameError" class="text-danger"></span> </div> <div class="mb-3"> <label for="armsshort" class="form-label">Arms ShortName:</label> <input type="text" class="form-control" id="arms_short_name" name="arms_short_name" autocomplete="off" value="<?php if(isset($_POST['arms_short_name'])){ echo $_POST['arms_short_name']; } ?>"> <span id="shortNameError" class="text-danger"></span> </div> </div> <div class="modal-footer modal-bg"> <button type="button" class="btn btn-outline-light btn-sm" data-bs-dismiss="modal"> Close </button> <button type="submit" class="btn btn-dark btn-sm">Submit</button> </div> </form> </div> </div> </div> My script <script> //Modal $('#submitForm').submit(function(event) { event.preventDefault(); $("#armsModal").on("hidden.bs.modal", function() { $('#longNameError').text(''); $('#shortNameError').text(''); $("#submitForm")[0].reset(); }); $('#armsModal').on('hidden.bs.modal', function () { // Clear form fields $('#submitForm')[0].reset(); // Clear error messages $('.invalid-feedback').text(''); }); // Get form data var formData = { 'arms_long_name': $('#arms_long_name').val(), 'arms_short_name': $('#arms_short_name').val() }; // AJAX request $.ajax({ type: 'POST', url: 'school-arms-action.php', data: formData, dataType: 'json', encode: true }) .done(function(data) { if (!data.success) { if (data.errors.arms_long_name) { $('#longNameError').text(data.errors.arms_long_name); } if (data.errors.arms_short_name) { $('#shortNameError').text(data.errors.arms_short_name); } }else{ modalMessage.innerHTML = '<div class="alert alert-success text-center text-black">ARMS SAVE SUCCESSFUL!</div>'; setTimeout(function() { window.location.href = 'school-arms'; }, 2000); // 2 seconds delay } }); }); </script> My school-arms-action.php $response = array('success' => false, 'errors' => array()); if ($_SERVER['REQUEST_METHOD'] === 'POST') { $arms_long_name = ucwords($_POST['arms_long_name']); $arms_short_name = strtoupper($_POST['arms_short_name']); $arms_id = mt_rand(100, 999); // Validation if (empty($arms_long_name)) { $response['errors']['arms_long_name'] = 'Arms LongName is Required.'; } if (empty($arms_short_name)) { $response['errors']['arms_short_name'] = 'Arms ShortName is Required.'; } // If no errors, proceed to submission if (empty($response['errors'])) { try { $pdo = new PDO("mysql:host=localhost;dbname=db_name", "username", "password"); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $table=$pdo->query("ALTER TABLE tbl_school_arms AUTO_INCREMENT = 1"); $table->execute(); $stmt = $pdo->prepare(" SELECT * FROM tbl_school_arms WHERE arms_name_long = :arms_name_long OR arms_name_short = :arms_name_short "); $stmt->bindParam(':arms_name_long', $arms_long_name, PDO::PARAM_STR); $stmt->bindParam(':arms_name_short', $arms_short_name, PDO::PARAM_STR); $stmt->execute(); $existingEntry = $stmt->fetch(PDO::FETCH_ASSOC); if ($existingEntry) { //This is what i used but not the right thing i want $response['errors']['arms_long_name'] = 'Duplicate Entry'; } else { // Perform database operations using PDO $stmt = $pdo->prepare(" INSERT INTO tbl_school_arms (arms_id, arms_name_long, arms_name_short) VALUES (:arms_id, :arms_name_long, :arms_name_short)"); $stmt->bindParam(":arms_id", $arms_id); $stmt->bindParam(":arms_name_long", $arms_long_name); $stmt->bindParam(":arms_name_short", $arms_short_name); $stmt->execute(); if($stmt->rowCount()){ $response['success'] = true; } } } catch (PDOException $e) { echo "Error: " . $e->getMessage(); } } } echo json_encode($response);

Hello PHP freaks I'm a newbie so bear with me. I have a generic problem and I'm sure code already exists to solve it. I need to have a dynamic sql string variable that will only produce an insert statement with database column names corresponding to input fields on the form that contain values, ignoring the blank ones. I know some looping function like foreach might be used, but if I use it on the $_POST array I'll get the values, not the keys. (the first field, well_no, is a primary key and is held in a session variable, so I know I'll be inserting a value for that column) $ins = "well_no,"; foreach($_POST as $field) if (!empty($field)){ $ins .=$_POST[$field].","; } And I get a warning "Warning: Undefined array key "production in... Here "production" is actually a form input. the problem is my insert statement is hard-coded: $sql = "INSERT INTO well_parent (well_no, easting, northing, purpose, country, admin1, admin2, admin3, settlement, orig_wellno, date_completed, coord_sys, elev, status) VALUES (:well_no, :easting, :northing, ... How can I adjust the sql statement and also the $data array holding the variables. Here's the context: $sql = "INSERT INTO well_parent (well_no, easting, northing, purpose, country, admin1, admin2, admin3, settlement, orig_wellno, date_completed, coord_sys, elev, status) VALUES (:well_no, :easting, :northing, :purpose, :country, :admin1, :admin2, :admin3, :settlement, :orig_wellno, :date_completed, :coord_sys, :elev, :status)"; $stmnt = $pdo->prepare($sql); $data = [':well_no'=>$well_no, ':easting'=>$easting, ':northing'=>$northing, ':purpose'=>$purpose, ':country'=>$country, ':admin1'=>$admin1, ':admin2'=> $admin2, ':admin3'=>$admin3, ':settlement'=>$settlement, ':orig_wellno'=>$orig_wellno, ':date_completed'=> $date_completed, ':coord_sys'=> $coord_sys, ':elev'=>$elev, ':status'=>$status]; $stmnt->execute($data);

wrote a stored procedure this morning and i don’t know how to get the values out of it through a class function in php or phpmyadmin. here is what i wrote : public function totalProcedures($friend_name,$session_id) { /* *query to fetch stored procedure */ try { //executing the stored procedure $sql_sp="CALL timeline (:friend, :session,@updates, @group_posts)"; $stmt_sp= $this->_db->prepare($sql_sp); $stmt_sp->bindValue(":friend",$friend_name); $stmt_sp->bindValue(":session",$session_id); $stmt_sp->execute(); $rows=$stmt_sp->fetch(PDO::FETCH_ASSOC); $stmt_sp->closeCursor(); // closing the stored procedure //trying to get values from OUT parameters. $stmt_sp_2=$this->_db->prepare("select @updates,@group_posts"); $stmt_sp_2->execute(); return $stmt_sp_2->fetch(PDO::FETCH_ASSOC); } catch (PDOException $ei) { echo $ei->getMessage(); } } can someone helpme how to get results. here is the storedprocedure: DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `timeline`(IN `friend` VARCHAR(255), IN `session_id` VARCHAR(255), OUT `updates` VARCHAR(62555), OUT `group_posts` VARCHAR(62555)) BEGIN select * FROM updates where author in (friend,session_id) order by time desc limit 5; select * FROM group_posts where author_gp in (friend,session_id) order by pdate desc limit 5; END$$ DELIMITER ; i get the result in php myadmin as follows: how do i do this inside a php class function. CALL timeline('shan2batman','aboutthecreator', @updates, @group_posts);

So I am working with a script that allows load more scrolling, much like youtube's comments section. The script I found uses mysqli and I am using PDO. So I need a bit of help converting some code below. Origional mysqli $results = mysqli_query($connecDB,"SELECT COUNT(*) FROM paginate"); $get_total_rows = mysqli_fetch_array($results); //total records //break total records into pages $total_pages = ceil($get_total_rows[0]/$item_per_page); My PDO code $results = DB::getInstance()->query("SELECT COUNT(*) FROM guestbook"); $get_total_rows = $results->results(); //break total records into pages $total_pages = ceil($get_total_rows[0]/$item_per_page); With the code above, I get the following error. "Object of class stdClass could not be converted to int"

Hello, I've followed the whole series of Log in and registration system on the phpacademy channel on Youtube: http://www.youtube.com/playlist?list=PLE134D877783367C7 After reading some comments and doing some research I found that it uses md5 and old mysql_ function, which for some reason are 'deprecated' or not secure. I'm still not sure. Also, after doing some searching I came across: http://www.sunnytuts.com/article/login-and-registration-with-object-oriented-php-and-pdo which is another tutorial that is quite similar to the one on phpacademy, but it uses PDO and Object oriented programming and bcrypt instead of md5(). The problem is that I don't know OOP and I use procedural programming. So is it worth learning OOP and using bcrypt() instead of md5() or PDO instead of mysql? I mean, am I ok with using the code from phpacademy, or do I need to follow the other one and also learn OOP. Thank you very much.

I am trying to call a stored procedure that accepts parameter inserts. The original query was being run from asp and looked like this: EXEC [SearchForXML6b] @sessionID = 973543, @LineID = 892245, @SortOrder = 'YearPublished DESC, SortTitle ASC', @PageNumber = 1, @RecordsPerPage = 20 I am redesigning the website in PHP, and am using the PDO method for querying the database. I am using a windows server, but have configured php to work on it, and can return simple selects from the db using pdo no problem. From looking around for help for many hours, the accepted way to do what I need seems to be to do something like this: $sql = "{:retval = CALL SearchForXML6b (@sessionID = 973543, @LineID = 892245, @SortOrder = 'YearPublished DESC, SortTitle ASC', @PageNumber = 1, @RecordsPerPage = 20)}"; $stmt = $this->c->prepare( $sql ); $stmt->bindParam('retval', $retval, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 4); However this isn't working. I have tried using EXEC instead of CALL but apparently PDO doesn't accept this. I have heard talk of installing OBDC but it seems to be more of a LINUX focused driver. Does anyone know if it is possible to run stored procedures with my configuration? Note: In SQL Server Management Studio, the SQL executes perfectly when using 'EXEC', but gives an error when trying to use 'CALL' as I don't think it's valid TSQL. Thanks in advance

Someone gave me a script that works - but it doesn't work for me. I've been trying to debug it, but I'm striking out. First, let's start with a simple query: $sql= "SELECT COUNT(URL) AS num FROM people WHERE URL = :MyURL"; It works, where a URL is MySite/Carl_Sagan and $MyURL = 'Carl_Sagan' I have several similar queries for various websites and sections. I want to try to merge everything into a new website that will function kind of like a mini encyclopedia. So I use UNION ALL to link several queries together, like this... $sql = "SELECT SUM(num) FROM ( SELECT COUNT(URL) AS num FROM pox_topics WHERE URL = :MyURL UNION ALL SELECT COUNT(URL) AS num FROM people WHERE URL = :MyURL UNION ALL SELECT COUNT(Taxon) AS num FROM gz_life WHERE Taxon = :MyURL ) AS X"; $stmt = $pdo->prepare($sql); $stmt->bindParam(':MyUrl',$MyURL,PDO::PARAM_STR); $stmt->execute(); $Total = $stmt->fetch(); switch($Total['num']) { case 1: // ETC.

Here is my code, I am trying to create a function that connects to my local SQL DB using PDO instead of mysqli It looks like I can connect, the issue is that...I can't get my error message to show. "Could not connect to the database" will not come up, when I know that my user name and password are incorrect. I try changing the root name to test to get the error but it doesn't show up ....I can't see my mistake <?php //load test ...un comment exit test db con //exit ('test db con'); } class DB { protected static $con; private function _construct() { try{ self::$con = new PDO( 'mysql: host= localhost; dbname=testdb', 'root', 'password'); self::$con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); self::$con->setAttribute( PDO::ATTR_PERSISTENT, false ); } catch (PDOException $e) { echo "Could not connect to database."; exit; } } public static function getConnection(){ //If this instance was not beem started, start it. if(!self::$con){ new DB(); } //Returns Writeable db connection return self::$con; } } ?>

I have a database class that uses PDO and it all works until i need to do an update where I increment the value What I have is a table and one of the columns is called 'counter' and I need to update this table every time a page is visited This is the function that handles all the PDO and creates the query /** * update * @param string $table A name of table to insert into * @param string $data An associative array * @param string $where the WHERE query part */ public function update($table, $data, $where) { ksort($data); $fieldDetails = NULL; foreach($data as $key=> $value) { $fieldDetails .= "`$key`=:$key,"; } $fieldDetails = rtrim($fieldDetails, ','); $sth = $this->prepare("UPDATE $table SET $fieldDetails WHERE $where"); foreach ($data as $key => $value) { $sth->bindValue(":$key", $value); } $sth->execute(); } and this is how I call the function by passing parametres to the function $board = ucwords(str_replace('-',' ',$board)); $sql = "SELECT boardid from boards WHERE boardname = :board"; $rows = $this->db->select($sql, array(':board' => $board)); $postData = array( 'counter' => 'counter+1', ); $this->db->update('topics', $postData, "`topicid` = {$topic} AND `boardid` = {$rows[0]['boardid']}"); It fails the update with this error message The counter column is of type INT

hi, guys i'm trying to create a commenting system to posts in a example site. And i can't seem to print the exact comments to its original post. i created two tables updates and comment_update and kept os_id as the common table row, where it is refering to update_id of updates table. everything seems ok except for the loops which is screwing up the output. Any advice on how to rectify it as i tried with while & other loops everything gives the same problem. here is my code for that logic: <?php $status_replies=""; $statusui_edit=""; $status2view=$project->statusView($_SESSION['uname']); foreach($status2view as $row){ $updateid=$row['update_id']; $account_name=$row['account_name']; $os_id=$row['os_id']; $author=$row['author']; $post_date=$row['time']; $title= stripslashes($row['title']); $data= stripslashes($row['update_body']); $statusdeletebutton=''; $sql1="select * from updates,comment_update where comment_update.os_id like updates.update_id and comment_update.type like 'b'"; $sql2="select * from updates left join comment_update using (os_id) where comment_update.os_id=:statusid "; $stmth=$conn->prepare($sql2); // $stmth->bindparam(":either",$_SESSION['uname']); //$stmth->bindparam(":statusid",$updateid); $stmth->execute(array( ":statusid"=>$updateid)); $status_reply= $stmth->fetchAll(PDO::FETCH_ASSOC); foreach ($status_reply as $row) { $status_reply_id=$row['comment_id']; $reply_author=$row['author']; $reply_d=htmlentities($row['comment_body']); $reply_data= stripslashes($reply_d); $reply_osid=$row['os_id']; $reply_date=$row['time']; $reply_delete_button=""; if ($reply_author==$_SESSION['uname'] || $account_name==$_SESSION['uname']) { $reply_delete_button.="<li><span id='$status_reply_id' class='delete_reply_btn glyphicon glyphicon-remove'><a href='#' title='Delete this comment'>Remove X</a></span></li>"; } if ($updateid==$reply_osid) { $status_replies="<div class='replyboxes pull-left reply_".$status_reply_id."'>Reply by:-<a href='search_results.php?u=".$reply_author."'>".$reply_author."</a>" . "<span class='pull-right'>".$reply_date . "<b class='caret'> <small><span class='btn-xs btn-danger dropdown-toggle pull-right' data-toggle='dropdown' aria-expanded='true' ><span class='glyphicon glyphicon-edit'></span> <ul class='dropdown-menu'>".$reply_delete_button . "<li><a href='#' class='hidden_text_area glyphicon glyphicon-pencil reply_".$status_reply_id."' title='Edit this comment' >Edit</a></li>" . "<li><a href='report.php?u='".$reply_author."'>Report</a><li></ul>" . "</span></span></small></b><br><legend>". html_entity_decode($reply_data)."</legend><br></div>"; } else { $status_replies=""; } } //insert_status_ui script to get message. if ($author==$_SESSION['uname'] || $account_name==$_SESSION['uname']) { $statusdeletebutton='<li>' . '<a href="#" type="'.$updateid.'" class="delete_4_session hidden_text_delete_'.$updateid.' glyphicon glyphicon-trash delete_reply_btn" title="Delete this status and its replies">Remove</a></li>'; } $status_list= $statusui_edit.'<div attr="'.$updateid.'" type="'.$updateid.'" class="statusboxes status_'.$updateid.' jumbotron">' . '<h3 style="color:black; margin-bottom:5px; margin-top:5px;" class="pull-left">' . '<div id="'.$updateid.'" class="title_s_2copy" value="'.html_entity_decode($title).'">'.html_entity_decode($title).'</div></h3>' . '<span class="pull-right">' . '<div class="dropdown">' . '<button type="button" class="btn btn-danger dropdown-toggle" data-toggle="dropdown" >' . '<span class="glyphicon glyphicon-edit"></span></button>' . '<ul class="dropdown-menu">' . '<li><a href="#" attr="'.$updateid.'" type="'.$updateid.'" class="edit_4_session hidden_text_edit glyphicon glyphicon-pencil" title="Edit this status" >Edit</a></li>'.$statusdeletebutton.'</ul></div></span><br><hr>' . '<legend><span class=" data_s_2copy" type="'.$updateid.'" >' . html_entity_decode($data).'</span><br><br></legend><b style="text-align:right; color:black;"><small>Posted by:- <a href="search_results.php?u='.$author.'">'.$author. '</a> '.$post_date.'</small></b>' . '<br><p>'.$status_replies.'</p><br>'; $status_list.= '<textarea id="reply_textarea_'.$updateid.'" class="status_reply_'.$updateid.' input-custom2" placeholder="comment\'s"></textarea>' . '<button id="reply_btn_'.$updateid.'" attr="'.$updateid.'" type="b" class="btn btn-warning pull-right btn-sm reply_btn reply_'.$updateid.'">Reply</button></div>'; echo $status_list; } ?>