Search the Community

Hi guys! I have a slight problem. When I pass values as variables to a sql statement it doesnt work. This is the example: THIS WORKS: <?php require 'DB/dbinc.php'; try { // Connect and create the PDO object $conn = new PDO("mysql:host=$dbhost; dbname=$dbname", $usernm, $dbpass); $conn->exec("SET CHARACTER SET utf8"); // Sets encoding UTF-8 // changes data in "text" and "text" where title = some title $sql = "UPDATE bloging SET title='Novi Title', tekst='Novi tekst' WHERE title='Update post'"; $count = $conn->exec($sql); $conn = null; // Disconnect } catch(PDOException $e) { echo $e->getMessage(); } // If data added ($count not false) displays the number of rows added if($count !== false) echo 'Number of rows added: '. $count; ?> THIS DOES NOT WORK <?php require 'DB/dbinc.php'; $oldTitle = 'Stari naslov'; $nTitle = 'novinaslov'; $nText = 'novitekst'; try { // Connect and create the PDO object $conn = new PDO("mysql:host=$dbhost; dbname=$dbname", $usernm, $dbpass); $conn->exec("SET CHARACTER SET utf8"); // Sets encoding UTF-8 // changes data in "text" and "text" where title = some title $sql = "UPDATE bloging SET title=$nTitle, tekst=$nText WHERE title=$oldTitle"; $count = $conn->exec($sql); $conn = null; // Disconnect } catch(PDOException $e) { echo $e->getMessage(); } // If data added ($count not false) displays the number of rows added if($count !== false) echo 'Number of rows added: '. $count; ?> I don't get it why it wont accept variable instead of string text as a value? Thanx in advance!

Sorry about any formatting inconsistencies here. This question was originally posted on stackoverflow, but none of the suggestions there solved my issue so I thought I'd bring it up here. Some of the information may be extraneous, but was asked for on SO, so I thought it might be helpful. I'm getting this error when I try to start PHP via command line on Fedora 20: PHP Warning: PHP Startup: Unable to load dynamic library '/usr/lib64/php/modules/pdo_odbc.so' - /usr/lib64/php/modules/pdo_odbc.so: undefined symbol: pdo_parse_params in Unknown on line 0 Here are the PHP packages I have installed: php.x86_64 5.5.7-1.fc20 @updates php-ZendFramework.noarch 1.12.3-3.fc20 @updates php-bcmath.x86_64 5.5.7-1.fc20 @updates php-cli.x86_64 5.5.7-1.fc20 @updates php-common.x86_64 5.5.7-1.fc20 @updates php-devel.x86_64 5.5.7-1.fc20 @updates php-gd.x86_64 5.5.7-1.fc20 @updates php-mcrypt.x86_64 5.5.7-1.fc20 @updates php-mssql.x86_64 5.5.7-1.fc20 @updates php-odbc.x86_64 5.5.7-1.fc20 @updates php-pdo.x86_64 5.5.7-1.fc20 @updates php-pear.noarch 1:1.9.4-23.fc20 @fedora php-pecl-jsonc.x86_64 1.3.3-1.fc20 @updates php-pecl-jsonc-devel.x86_64 1.3.3-1.fc20 @updates php-process.x86_64 5.5.7-1.fc20 @updates php-xml.x86_64 5.5.7-1.fc20 @updates I have `extension=pdo_odbc.so` and `extension=oci8.so` as the only extensions defined in my `/etc/php.ini` file. There are also a lot of .ini files in the `/etc/php.d` directory that loads most of the other extensions. The following .so files are in the `/usr/lib64/php/modules` directory: -rwxr-xr-x. 1 root root 32560 Dec 10 23:51 bcmath.so -rwxr-xr-x. 1 root root 24696 Dec 10 23:51 bz2.so -rwxr-xr-x. 1 root root 33752 Dec 10 23:51 calendar.so -rwxr-xr-x. 1 root root 15568 Dec 10 23:51 ctype.so -rwxr-xr-x. 1 root root 86912 Dec 10 23:51 curl.so -rwxr-xr-x. 1 root root 180608 Dec 10 23:51 dom.so -rwxr-xr-x. 1 root root 65496 Dec 10 23:51 exif.so -rwxr-xr-x. 1 root root 2713328 Dec 10 23:51 fileinfo.so -rwxr-xr-x. 1 root root 53624 Dec 10 23:51 ftp.so -rwxr-xr-x. 1 root root 120904 Dec 10 23:51 gd.so -rwxr-xr-x. 1 root root 15640 Dec 10 23:51 gettext.so -rwxr-xr-x. 1 root root 45080 Dec 10 23:51 iconv.so -rwxr-xr-x. 1 root root 40840 Dec 12 09:13 json.so -rwxr-xr-x. 1 root root 45256 Dec 10 23:51 mcrypt.so -rwxr-xr-x. 1 root root 53816 Dec 10 23:51 mssql.so -rwxr-xr-x. 1 root root 560751 Jan 10 10:20 oci8.so -rwxr-xr-x. 1 root root 70312 Dec 10 23:51 odbc.so -rwxr-xr-x. 1 root root 25008 Dec 10 23:51 pdo_dblib.so -rwxr-xr-x. 1 root root 28856 Dec 10 23:51 pdo_odbc.so -rwxr-xr-x. 1 root root 116240 Dec 10 23:51 pdo.so -rwxr-xr-x. 1 root root 29168 Dec 10 23:51 pdo_sqlite.so -rwxr-xr-x. 1 root root 272000 Dec 10 23:51 phar.so -rwxr-xr-x. 1 root root 32880 Dec 10 23:51 posix.so -rwxr-xr-x. 1 root root 15624 Dec 10 23:51 shmop.so -rwxr-xr-x. 1 root root 54176 Dec 10 23:51 simplexml.so -rwxr-xr-x. 1 root root 91368 Dec 10 23:51 sockets.so -rwxr-xr-x. 1 root root 51336 Dec 10 23:51 sqlite3.so -rwxr-xr-x. 1 root root 19880 Dec 10 23:51 sysvmsg.so -rwxr-xr-x. 1 root root 11496 Dec 10 23:51 sysvsem.so -rwxr-xr-x. 1 root root 15720 Dec 10 23:51 sysvshm.so -rwxr-xr-x. 1 root root 19712 Dec 10 23:51 tokenizer.so -rwxr-xr-x. 1 root root 36720 Dec 10 23:51 wddx.so -rwxr-xr-x. 1 root root 32888 Dec 10 23:51 xmlreader.so -rwxr-xr-x. 1 root root 54072 Dec 10 23:51 xml.so -rwxr-xr-x. 1 root root 49152 Dec 10 23:51 xmlwriter.so -rwxr-xr-x. 1 root root 37104 Dec 10 23:51 xsl.so The output of when I run phpinfo() can be found here. Contents of `pdo_odbc.ini` at /etc/php.d: ; Enable pdo_odbc extension module extension=pdo_odbc.so Output of `readelf -Ws pdo_odbc.so`: Symbol table '.dynsym' contains 83 entries: Num: Value Size Type Bind Vis Ndx Name 0: 0000000000000000 0 NOTYPE LOCAL DEFAULT UND 1: 0000000000001ac0 0 SECTION LOCAL DEFAULT 9 2: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND convert_to_long 3: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND pdo_parse_params 4: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND php_pdo_unregister_driver 5: 0000000000000000 0 FUNC GLOBAL DEFAULT UND free@GLIBC_2.2.5 (2) 6: 0000000000000000 0 FUNC GLOBAL DEFAULT UND strcasecmp@GLIBC_2.2.5 (2) 7: 0000000000000000 0 NOTYPE WEAK DEFAULT UND _ITM_deregisterTMCloneTable 8: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLParamData 9: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND cfg_get_string 10: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLRowCount 11: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND _estrdup 12: 0000000000000000 0 FUNC GLOBAL DEFAULT UND strlen@GLIBC_2.2.5 (2) 13: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLFreeHandle 14: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLFetchScroll 15: 0000000000000000 0 FUNC GLOBAL DEFAULT UND __stack_chk_fail@GLIBC_2.4 (3) 16: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND _zval_dtor_func 17: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLDriverConnect 18: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLPutData 19: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND zend_fetch_resource 20: 0000000000000000 0 FUNC GLOBAL DEFAULT UND strchr@GLIBC_2.2.5 (2) 21: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND php_info_print_table_row 22: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND _php_stream_stat 23: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLGetDiagRec 24: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLDescribeCol 25: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND _erealloc 26: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND php_pdo_get_exception 27: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND zend_hash_index_find 28: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND zend_declare_class_constant_long 29: 0000000000000000 0 NOTYPE WEAK DEFAULT UND __gmon_start__ 30: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLCloseCursor 31: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLSetEnvAttr 32: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND php_error_docref0 33: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND php_info_print_table_end 34: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND _safe_malloc 35: 0000000000000000 0 FUNC GLOBAL DEFAULT UND memcpy@GLIBC_2.14 (4) 36: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLConnect 37: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND zend_throw_exception_ex 38: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND php_file_le_stream 39: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLDescribeParam 40: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND php_info_print_table_header 41: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND php_pdo_register_driver 42: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLColAttribute 43: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND php_pdo_get_dbh_ce 44: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLPrepare 45: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND _ecalloc 46: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLSetConnectAttr 47: 0000000000000000 0 FUNC GLOBAL DEFAULT UND __strcpy_chk@GLIBC_2.3.4 (5) 48: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND _convert_to_string 49: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLExecute 50: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND _efree 51: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLExecDirect 52: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND zval_is_true 53: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLSetCursorName 54: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLDisconnect 55: 0000000000000000 0 NOTYPE WEAK DEFAULT UND _Jv_RegisterClasses 56: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND _php_stream_read 57: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND add_next_index_string 58: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND _estrndup 59: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLEndTran 60: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND _emalloc 61: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLNumResultCols 62: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLGetData 63: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND php_file_le_pstream 64: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND _zval_copy_ctor_func 65: 0000000000000000 0 NOTYPE WEAK DEFAULT UND _ITM_registerTMCloneTable 66: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLBindParameter 67: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLGetCursorName 68: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND add_next_index_long 69: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLSetStmtAttr 70: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLAllocHandle 71: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND php_info_print_table_start 72: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLBindCol 73: 0000000000000000 0 FUNC WEAK DEFAULT UND __cxa_finalize@GLIBC_2.2.5 (2) 74: 0000000000000000 0 NOTYPE GLOBAL DEFAULT UND spprintf 75: 0000000000000000 0 FUNC GLOBAL DEFAULT UND strstr@GLIBC_2.2.5 (2) 76: 0000000000000000 0 FUNC GLOBAL DEFAULT UND SQLMoreResults 77: 0000000000206438 0 NOTYPE GLOBAL DEFAULT 23 _edata 78: 0000000000206440 0 NOTYPE GLOBAL DEFAULT 24 _end 79: 0000000000002310 8 FUNC GLOBAL DEFAULT 11 get_module 80: 0000000000206438 0 NOTYPE GLOBAL DEFAULT 24 __bss_start 81: 0000000000001ac0 0 FUNC GLOBAL DEFAULT 9 _init 82: 00000000000045a4 0 FUNC GLOBAL DEFAULT 12 _fini

Hello. I am new. I have simple two step process I want to be done. When it does work the webpage keeps going (im guessing it's an endless loop)... I can't seem to get it to work: I know there's something I'm missing about this PDO array stuff. <?php include("database.php"); $time_ago = strtotime("-1 minute"); /*find subscribers over a month old with the thirtydaysubs table*/ $checker = "SELECT emailaddress FROM aa_thirtydaysubs WHERE subscribedate < $time_ago"; $sthandler = $database->prepare($checker); $sthandler->execute(); /*then move it to main list and remove from 30 day list*/ $data = array(); $data = $sthandler->fetch(); if ($sthandler->columnCount()){ while($sthandler->fetchAll(PDO::FETCH_ASSOC) !== 0){ $email2 = $data->fetchColumn(0); $newlist= "2"; $updatelistid = "UPDATE zzemail_list_subscribers set listid = :newlist WHERE emailaddress = :email"; $sthandler3 = $database->prepare($updatelistid); $sthandler3->execute(array(':email' => $email2, ':newlist' => $newlist )); $clearars = "DELETE FROM aa_thirtydaysubs WHERE emailaddress= :email"; $sthandler6 = $database->prepare($clearars); $sthandler6->execute(array(':email' => $email2)); } } ?>

0down votefavorite I just converted a query that displays "bread crumbs" style navigation links to PDO: function get_path($dbh,$node,$TopnavTable, $TopnavName) { $stmt = $dbh->prepare("SELECT name FROM $TopnavTable WHERE $TopnavName = ?"); $stmt->bindValue(1,$node); $stmt->execute(); $stmt->setFetchMode(PDO::FETCH_ASSOC); $row = $stmt->fetch(); $path = array_merge(get_path($pdo,$row['Parent'], $TopnavTable, $TopnavName), $path); return $path ; } I also figured out a way to display the results: $Path2 = explode("/", $path); $Path2[1] = '<a href="/Topics">'.$Path2[1].'</a>'; $Path2[2] = '<a href="/Topics/'.$Path2[2].'">'.$Path2[2].'</a>'; $Path2[3] = '<span class="navHere"><b>'.$Path2[3].'</b></span>'; echo join( $Path2, ' > ' ); However, it only works only if I'm working with an array consisting of three segments. For example, I'm viewing the URL MySite/Topics/Washington/Governor, which displays the following bread crumbs trail: Topics > Washington > Governor If I view MySite/Washington, it should display... Topics > Washington But I get an error message: Undefined offset: 3, plus there's a trailing arrow (>) after Washington. So I'm trying to figure out how to make this work with any number of segments - 2, 3, 6, etc. Regardless of the number of segments, I'd like the last segment to be unlinked. (I'm going to further put it inside a span.) Does anyone have any tips? (If there's a completely different way of doing it that's better, I'd love to hear about it.)

Wow I have to say its been a long time since I posted here but I do need alittle help so here we go: I got this script or created most of it (I dont remember) for me to connect mysql using PDO. The script works fine exept when i try to update a column in my db. now i get no errors but it does not update. $superglobals = array($_POST); foreach ($superglobals as $value){ foreach ($value as $k => $v){ $post[$k]=$v; } } $sql = "UPDATE ".DB_PREFIX."_dbase SET value = :value WHERE key = :key"; foreach ($post as $key => $value) { $bind = array("value"=> $value, "key"=>$key); $db->execute($sql, $bind) or die($db->error()); } I have upload the db class and gave you what im trying to do please help Here is the database CREATE TABLE db_dbase ( key text, value text ) database.php

I am trying to get all the "posts" from a users friends list on their news feed. I'm trying to do so by getting all those posts where the user is the initiator_user_id and get all the posts from the friend_user_id then I want to get all the posts from initiator_user_id where the user's id is the friend_user_id. This is what I have that's messing up: $wallsql= $conn->prepare('SELECT * FROM activity f INNER JOIN wp_bp_friends n1 ON (n1.initiator_user_id=f.user_id) INNER JOIN wp_bp_friends n2 ON (n2.friend_user_id=f.user_id) WHERE (n1.friend_user_id=:userid) OR (n2.initiator_user_id=:userid) ORDER BY datetime DESC LIMIT 8'); $wallsql->bindParam(':userid', $_SESSION['uid']); $wallsql->execute(); Previously this works but it only gets the posts where the user is the initiator_user_id: $wallsql= $conn->prepare('SELECT * FROM activity f INNER JOIN wp_bp_friends n2 ON n2.friend_user_id=f.user_id WHERE n2.initiator_user_id=:userid ORDER BY datetime DESC LIMIT 8'); Any help would be much appreciated!

How can i add total of user's following me? The code I currently have displays ALL the user's following me, instead of saying *numbers of user's following me* <?php $friends = Friends::getFriendsForUser($data->id); if (count($friends) > 0) { $db = DB::getInstance(); foreach($friends as $friend_id) { $friend = $db->query('SELECT name, username FROM users WHERE id = ?', array($friend_id)); if ($friend->count() == 1) { echo '<table> <tr> <td><img src="images/avatar.png"></td> <td><a href="profile.php?user='.escape($friend->first()->username).'">'.$friend->first()->name.'</a></td> </tr> </table>'; } } } else { echo 'Not following anyone.'; } ?>

I've been looking at this for a bit and can't seem to figure what's going on? I have used this code before and never had this issue.... I'm tryig to populate a dropdown menu from a MySql DB. The dropdown menu seems to skip the first array... I attached a screenshot that hopefully show's what I'm talking about.. any thoughts?

I'm having a problem with PHP and a stored procedure in SQL Server 2005. I haven't really touched stored procedures before and haven't used them with php. I can't retrieve a value from a table that I know is definitely there. I have a simple login table, what I want the stored procedure to do is to take a username as an input and then output that user's password salt. I want the stored procedure to use parameterised values to avoid SQL injection. What happens at the moment is when the php page is run, nothing is returned and the SQL Profiler displays the following error: User Error Message: Incorrect Syntax near '@Username' While this error message should tell me something is wrong, I can't for the life of me see where the incorrect syntax is. Copy of the Php: $user = "usercm"; $password ="cmuserpassword"; try{$conn = new PDO("odbc:DRIVER={SQL Server Native Client 10.0};SERVER=TestingServer;DATABASE=TestingDatebase;",$user,$password);} catch(PDOException $e){echo "oh no";} $username = "sdct"; $prepusp = $conn->prepare("EXEC uspReturnSalt(?,?)"); $prepusp->bindParam(1, $username, PDO::PARAM_STR); $prepusp->bindParam(2, $usersalt, PDO::PARAM_STR, 450); $prepusp->execute(); Copy of the Stored Procedure: ALTER PROCEDURE [dbo].[uspReturnSalt] @Username NVARCHAR(100), @Usersalt NVARCHAR(450) OUTPUT AS BEGIN DECLARE @sqlcmd NVARCHAR(MAX); DECLARE @params NVARCHAR(MAX); SET @sqlcmd = N'SELECT @Usersaltone = salt FROM CMUsers WHERE username = @Usernameone'; SET @params = N'@Usernameone NVARCHAR(100), @Usersaltone NVARCHAR(450) OUTPUT'; EXECUTE sp_executesql @sqlcmd, @params, @Usernameone = @Username, @Usersaltone = @Usersalt OUTPUT; END To clarify, the server this is on runs Windows 2003 so I cannot use the sqlsrv drivers as they require SQL Server Native Client 2012 which is incompatible. It is impossible to upgrade the operating system (server isn't mine) so I can't use any php drivers that require SQL Server Native Client 2012. If anyone could help I would be eternally grateful. Here is the sql profiler messages before and after that error: RPC:Completed | exec [sys].sp_sproc_columns_90 N'uspReturnSalt' ,@ODBCVer=3 RPC:Starting | declare @p1 int set @p1 =NULL exec sp_prepare @p1 output,N'@Username nvarchar(100),@P2 text OUTPUT' ,N'EXEC uspReturnSalt(@Username,@P2 OUTPUT)' ,1 select @p1 Exception | Error: 102, Severity: 15, State: 1 User Error Message | Incorrect syntax near '@Username'. SP:CacheMiss | (@Username nvarchar(100),@P2 text OUTPUT)EXEC uspReturnSalt(@Username,@P2 OUTPUT) Exception | Error: 8180, Severity: 16, State: 1 User Error Message | Statement(s) could not be prepared RPC:Completed | declare @p1 int set @p1=NULL exec sp_prepare @p1 output, N'@Username nvarchar(100),@P2 text OUTPUT' ,N'EXEC uspReturnSalt(@Username,@P2 OUTPUT)',1 select @p1

Hello, I have a little problem. This is what I want to achive: I have 2 mysql tables (categories, channels), the channel table has a cat_id in it. I want to update/edit a product and place it in another category but the code that I've made shows just one catedory (id=1) even if the product has a parent id of 5. try { //prepare query $query = "select channel_id, name, category_id from channels where channel_id = ? limit 0,1"; $stmt = $pdo->prepare( $query ); //this is the first question mark $stmt->bindParam(1, $_REQUEST['id']); //execute our query $stmt->execute(); //store retrieved row to a variable $row = $stmt->fetch(PDO::FETCH_ASSOC); //values to fill up our form $channel_id = $row['channel_id']; $name = $row['name']; $category_id = $row['category_id']; }catch(PDOException $exception){ //to handle error echo "Error: " . $exception->getMessage(); } $query2 = "SELECT * FROM categories"; $stmt2 = $pdo->prepare( $query2 ); $stmt2->execute(); $results = $stmt2->fetchAll(PDO::FETCH_ASSOC); ?> <!--we have our html form here where new user information will be entered--> <form action='#' method='post' border='0'> <table> <tr> <td>Channel Name</td> <td><input type='text' name='name' value='<?php echo $name; ?>' /></td> </tr> <tr> <td>Category</td> <td> <?php foreach($results as $rows) {?> <select name="fileselect"> <option name='cat_id' value=" <?php echo $rows['category_id']; ?>"> <?php echo $rows['name']; ?></option> <!-- <input type='text' name='category_id' value='<?php //echo $category_id; ?>' /> --> <?php } ?> </select> </td> </tr> <tr> <td></td> <td> <!-- so that we could identify what record is to be updated --> <input type='hidden' name='channel_id' value='<?php echo $channel_id ?>' /> <!-- we will set the action to edit --> <input type='hidden' name='action' value='update' /> <input type='submit' value='Edit' /> </td> </tr> </table> </form> The update parte is ommited as it works if there is no dropdown, just a plain text input.

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

I have a MySQL database on justhost with a field called "category" that I want to use in a WHERE query. The database opens fine without any WHERE clause. It's only when I begin to use a WHERE clause that I begin to get into trouble. All the "Gurus" out there tell me I need to be shifting over to PDO, so here is my attempt to get up to speed. The previous page (select_category.php) had a form with a selection drop-down box for category. I addressed it in the next file (quiz.php) like this: $cat = $_POST['category']; Now I want to query the data: $db = new PDO('mysql:host=localhost;dbname=' . $dbname , $dbusername, $pass); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql->prepare("SELECT * FROM QuestionsAnswers WHERE category = " . $cat); //This is line 23 foreach ($db->query($sql) as $row) { print '<p>' . $row['question'] .' <br /> '. $row['displaystring'] . '</p>'; } but I'm getting errors: I have been around and around on this for two days. Does anybody see what I am obviously missing? Thanks.

Is this the correct format to doing a MySQL full text search using PDO? I want to search about 4 tables for the same keyword and not sure of the syntax $count = "SELECT COUNT(*) FROM (SELECT t.boardid, t.topicid, 'messages' as mytable, topicname as title, message as content, MATCH(topicname, message) AGAINST(':keywords*' IN BOOLEAN MODE) as score FROM topics t INNER JOIN messages m ON t.topicid=m.topicid WHERE MATCH(topicname, message) AGAINST(':keywords*' IN BOOLEAN MODE) UNION ALL SELECT id,'', 'news' as mytable,title, content, MATCH(title, content) AGAINST(':keywords*' IN BOOLEAN MODE) as score FROM news WHERE MATCH(title, content) AGAINST(':keywords*' IN BOOLEAN MODE) UNION ALL SELECT id,'', 'events' as mytable,title, content, MATCH(title, content) AGAINST(':keywords*' IN BOOLEAN MODE) as score FROM events WHERE MATCH(title, content) AGAINST(':keywords*' IN BOOLEAN MODE) UNION ALL SELECT id,'', 'blogs' as mytable,title, content, MATCH(title, content) AGAINST(':keywords*' IN BOOLEAN MODE) as score FROM blogs WHERE MATCH(title, content) AGAINST(':keywords*' IN BOOLEAN MODE)) a GROUP BY title"; $keywords = strtolower($_SESSION['keywords']); $sth = $dbh->prepare($count); $sth->bindValue(':keywords', $keywords, PDO::PARAM_STR); $sth->execute();

I have a function that updates a database and it works fine for one record but i have a form which shows loads of rows with checkboxes and i want to do a batch update (I suppose the principle is the same for a batch delete). When I click a load of checkboxed and call the funtion to update, i get an error about an array to string conversion and also this error message "Unknown column 'Array' in 'where clause" I know a way around it but it's not great using a foreach loop to update the database but that involves loads of queries This is my function I call to update the database /** * 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(); } This works public function newsEdit($data) { $postData = array( 'title' => $data['news_title'], 'content' => $data['news_content'], 'photo' => $data['imgname'], 'keywords' => $data['news_keywords'], 'alternate' => $data['alternate'] ); $this->db->update('news', $postData, "`id` = {$data['newsid']}"); } $data = array(); $data['news_title'] = $_POST['news_title']; $data['news_content'] = $_POST['news_content']; $data['imgname'] = $_POST['imgname']; $data['news_keywords'] = $_POST['news_keywords']; $data['alternate'] = substr($_POST['imgname'],0,strrpos($_POST['imgname'], ".")); newsEdit($data); This doesn't public function newsArchive($ids) { $postData = array( 'archived' => 'Y' ); $this->db->update('news', $postData, "`id` IN ({$ids})"); } newsArchive($_POST['id']); What have i got to change to get the update working?

Hello everyone.. i am stuck with following code and i want to use it in PDO plz help me if($number>0) { $id=mysql_result($result,0,"id"); $_SESSION['id']=$id; somepage.php }

Hello, can someone please let me know if my code here is wrong? Im trying to upgrade old mysql to PDO. Just a simple update command and on a few values if the $variable = "" then make the value null in DB. thank you try{ $sql1="UPDATE newone SET corating=:corating, cotarg=:cotarg, coclose=:coclose, ROI=:ROI, est1=:est1, est2=:est2, basicos=:basicos, mktcap=:mktcap, coNav=:nav, WHERE coID=:coID"; $q1 = $dbh->prepare($sql1); $q1->execute(array( ':corating'=>$corating, ':cotarg'=>$cotarg, ':coclose'=>$coclose, ':ROI'=>$ROI, ':est1'=>isset($est1) ? $est1 : null, ':est2'=> isset($est2) ? $est2 : null, ':basicos'=>$basicos, ':mktcap'=>$mktcap, ':coNav'=>isset($nav) ? $nav : null, ':coID'=>$coID, )); } catch(PDOException $e) { echo 'ERROR: ' . $e->getMessage(); }

LAMP environment, PHP 5.4.13, MySQL 5.5.30. Code snippet: $db = new PDO('mysql:host=localhost;dbname=test',$username,$password); $sql="INSERT INTO `atable`( `user`, `expires`, `approved_by`,... ) VALUES ( :auser, :aexpires, :aapproved_by,... )"; $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $stmt = $db->prepare($sql); $stmt->bindValue(':auser', $buser, PDO::PARAM_STR); $stmt->bindValue(':aexpires', $bexpires, PDO::PARAM_STR); $stmt->bindValue(':aapproved_by', $bmyNull, PDO::NULL_EMPTY_STRING); ... $stmt->exec($sql) or die(print_r($db->errorInfo(), true)); Error: Fatal error: Call to undefined method PDOStatement::exec() in ... Any insights greatly appreciated!

Hello all, I am trying to make a determination about the best way to move forward with my database code for both personal projects and for the company I work for. For my own projects, I used to use mysql functions but have started to switch over to PDO. The company I work for is still using mysql functions and they are a bit wary about switching over to either mysqli or pdo, as they have a lot of code to go through and they don't want to make a bad decision. So I started working on a database class that would replicate all of the mysql functions they were used to (fetch_assoc, fetch_array, num_rows, etc) and decided to make it so that, via a config, you could use mysqli OR pdo and then, in each function, it would do it's best to get the expected result. This way, the code for the company would only ever have to do something like: $db->query($sql); if($db->num_rows() > 0) etc... But the more I read up on PDO (which I have been using in a more proceedural way), it seems like this is already a wrapper, so in effect I'm wrapping a wrapper. So, bottom line, what are others doing to future proff their database code? I like the idea of a class with generic function names, because if there is ever another style to use (mysqlii?) we can just write up the proper functions inside of the existing class and they'll work without a hitch. But is this something that pdo already delivers? I know it's good for dealing with multiple types of databases (mysql, mssql, oracle) but this company will only ever be working with mysql, so I don't have to go overboard with considerations for that. Thanks for any help/advice! JD

Hi, I am trying to get the rank of a user in a database, based on his score, so when the user logs in he can see his rank. I am using this to build a list of all users ordered by their score for a different section. $stmt = $dbh->query("SELECT * FROM $table ORDER BY score DESC"); $rank = 0; while($uid = $stmt->fetchObject()) { $rank++; echo $uid->uname ."is ranked". $rank; }; How can I get ONLY a users rank in the db based on his ID number, if I have a table like, . | user | ID | score | Jim | 2568 | 80 Pete | 5693 | 115 Sam | 3258 | 569 George | 7412 | 30 the ID numbers are a unique key If Jim logs in today he will see: Your Current Rank is 3 I am using PHP with PDO on a MySQL table. Thanks!

I am not a jquery guru in any sense, so I am seeking some help with the below code. What I am trying to do, is populate two input fields, based on what is selected from a dropdown field via a database call. When I select a term from the dropdown, I want the term start date and term end date to populate in the appropriate fields. First here is my form: <form class="form-horizontal margin-none" action="<?=BASE_URL;?>form/runSection/" id="validateSubmitForm" method="post" autocomplete="off"> <div class="control-group"> <label class="control-label"><font color="red">*</font> <?php _e( _t( 'Term' ) ); ?></label> <div class="controls"> <select style="width:100%;" name="termCode" id="select2_10" required> <option value=""> </option> <?php table_dropdown('term', 'termCode', 'termName'); ?> </select> </div> </div> <div class="control-group"> <label class="control-label"><?php _e( _t( 'Term Start/End' ) ); ?></label> <div class="controls"> <input type="text" name="termStartDate" id="termStartDate" disabled class="span6" required /> <input type="text" name="termEndDate" id="termEndDate" disabled class="span6" required /> </div> </div> Second here is the javascript section: <script type="text/javascript"> jQuery(document).ready(function(){ jQuery('#select2_10').live('change', function(event) { $.ajax({ type : 'POST', url : '<?=BASE_URL;?>section/runTermLookup/', dataType: 'json', data : $('#validateSubmitForm').serialize(), cache: false, success: function( data ) { for(var id in data) { $(id).val( data[id] ); } } }); }); }); </script> Third, here is the method from my controller which passes the $_POST['termCode'] to the method of the same name found in the model: public function runTermLookup() { if(!$this->_auth->isUserLoggedIn()) { redirect( BASE_URL ); } $data = array(); $data['termCode'] = isPostSet('termCode'); $this->model->runTermLookup($data); } Lastly, here is the method from my model: public function runTermLookup($data) { $bind = array(":term" => $data['termCode']); $q = DB::inst()->select( "term","termCode = :term","termID","termStartDate,termEndDate", $bind ); $r = $q->fetch(\PDO::FETCH_ASSOC); $json = array( 'input#termStartDate' => $r['termStartDate'], 'input#termEndDate' => $r['termEndDate'] ); echo json_encode($json); } I've been looking at this for hours, so a fresh pair of eyes is greatly appreciated. Thank you.

I have a form which has multi-value select fields (Bootstrap) in which I can use to submit an array of data to the database. Now, the issue is I am having trouble retrieving that array with json_decode(). The issue is my array format for returning values: <?php echo json_decode( $this->prog[0]['schoolKey']) ); ?> When echoing out the above, I get the follow error message: "stripslashes() expects parameter 1 to be string, array given in ..." I know there is a easy solution, but I've been working 12 hours long and I can barely see straight. Any help with this is greatly appreciated.

I am changing my test code from mysqli to PDO. It worked for one of my forms but the other I racking my brain as to why it isn't. I have stripped it down, found a few typos but array 0000 error is occurring and I am unsure how to resolve it. Can you help? James connection $dbtype = "mysql"; $dbhost = "localhost"; $dbname = "mydb1"; $dbuser = "myusr1"; $dbpass = "mp1"; try { // database connection $conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass); $conn -> exec('SET CHARACTER SET utf8'); } catch(PDOException $e) { echo 'There was a problem'; } php $sql = "UPDATE catalogue SET title=:title, manufacturer=:manufacturer WHERE id=:id"; $q = $conn->prepare($sql); $q->bindParam(':id',$_POST['id'], PDO::PARAM_INT); $q->bindParam(':title',$_POST['title'], PDO::PARAM_STR); $q->bindParam(':manufacturer',$_POST['manufacturer'], PDO::PARAM_INT); if ($q->execute()){ echo "Saved successfully"; } else { echo "<br/> Crap, something went wrong <br/>"; //just for testing echo $sql." <br/>"; print_r($_POST); print_r($conn->errorCode()); echo "<br/>"; print_r($conn->errorInfo()); echo "<br/>"; } output Crap, something went wrong UPDATE catalogue SET title=:title, manufacturer=:manufacturer WHERE id=:id Array ( [title] => Madinoz Pty Ltd [manufacturer] => 71 [media_id] => 1 [category_id] => 17 [subcategory_id] => Please Select [notes] => This is some text. [keywords] => Hardware, Handles, Hooks, Rails, Fittings, [save] => Save [id] => 2 ) 00000 Array ( [0] => 00000 [1] => [2] => )

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

Well I am having the worst weekend ever. I have been working on this code all day and google is not my friend today. So here is the problem, I am trying to write a form that is basically keeping records of people who have stayed as guest and what meals they ate (breakfast, lunch, dinner, midnight snack). So the html form is pretty simple, and I have a few checkboxes and a little javascript to add more rows if there are more guest. Everything is working fine, but the checkboxes values are killing me. Basically if the box isnt checked than do what? Anyway my insert isnt working at all Anyway here is some basic coding Html form for checkboxes: <td><input type="checkbox" name="breakfast[]" /></td> <td><input type="checkbox" name="lunch[]" /></td> <td><input type="checkbox" name="dinner[]" /></td> <td><input type="checkbox" name="midnight[]" /></td> <td><input type="checkbox" name="lodging[]"/></td> <td><input type="text" name="guestName[]"/></td> So as you can see checkboxes for the meals and a text input for the guest name. Now here is my function (please go easy I am new to pdo) public function createMeal($id,$table){ try{ //write query if(isset($_POST['guestName'])){ $guestName = $_POST['guestName']; $repName = $_POST['repName']; $repTitle = $_POST['repTitle']; if(isset($_POST['breakfast'])){ $breakfast = $_POST['breakfast']; } if(isset($_POST['lunch'])){ $lunch = $_POST['lunch']; } if(isset($_POST['dinner'])){ $dinner = $_POST['dinner']; } if(isset($_POST['midnight'])){ $midnight = $_POST['midnight']; } if(isset($_POST['lodging'])){ $lodging = $_POST['lodging']; } } foreach($guestName as $a => $b) { $query = "INSERT INTO meals(repName, repTitle, breakfast, lunch, dinner, midnight, lodging, guestName, masterLogID) VALUES (:repName, :repTitle, :breakfast, :lunch, :dinner, :midnight, :lodging, :guestName, :masterLogID)"; $stmt = $this->conn->prepare($query); $stmt->bindParam(':repName',$repName[$a]); $stmt->bindParam(':repTitle',$repTitle[$a]); $stmt->bindParam(':breakfast',$breakfast[$a]); $stmt->bindParam(':lunch',$lunch[$a]); $stmt->bindParam(':dinner',$dinner[$a]); $stmt->bindParam(':midnight',$midnight[$a]); $stmt->bindParam(':lodging',$lodging[$a]); $stmt->bindParam(':guestName',$guestName[$a]); $stmt->bindParam(':masterLogID',$masterLogID); // Execute the query $stmt->execute(); } echo "Record was saved."; }catch(PDOException $exception){ //to handle error echo "Error: " . $exception->getMessage(); } } I am getting the message "Record was saved", but nothing is in the table. Any help is greatly appreciated

I have a simple function public function showtrips($id,$table){ $sql="SELECT * FROM $table WHERE id = :id ORDER BY id DESC"; if(!$stmt = $this->conn->prepare($sql)){ // prepare failed echo "<pre>Prepare failed:\n"; print_r($pdo->errorInfo()); echo "</pre>"; } else { if(!$stmt->execute(array(':id'=>$id))){ // execute failed echo "<pre>Execute failed:\n"; print_r($stmt->errorInfo()); echo "</pre>"; } else { while($r = $stmt->fetch(PDO::FETCH_ASSOC)){ $data[]=$r; } return $data; } } } and a for loop that calls this function foreach($obj->showtrips($id,"trips") as $value){ extract($value); echo <<<show <tr class="success"> <td>$departTime</td> <td>$departPlace</td> <td>$arriveTime</td> <td>$arrivePlace</td> <td>$numberOfPass</td> <td>$purpose</td> <td>$cargo</td> <td>$remarks</td> </tr> show; } In my foreach loop how can I first test if the return variable $data has any data in it?