Here I is what I have so far:     And I am NOT SURE WHY Hakre closed this because it has to do with dropping MySQL events from the MySQL Event Scheduler NOT with using table names with PDO.  My code is as follows:  public function dMySQLEvent($my_env, $my_event_name) {          $my_success = 0;         $my_message = '';          try {              # declare &amp; set variables using $my_env             list ($db_dbdriver, $db_hostname, $db_database, $db_username, $db_password) = Connection::dbConnect($my_env);              # set default timezone              # create &amp; set connection              # WORKS                 # $conn-&gt;exec("DROP EVENT IF EXISTS " . $my_event_name . ";");              # DOES NOT WORK                 # Prepare an SQL statement                 $my_sql = "DROP EVENT IF EXISTS :My_Event_Name";                 $stmt = $conn-&gt;prepare($my_sql);              # bind parameters to prevent SQL Injection             $stmt-&gt;bindValue(':My_Event_Name', $my_event_name);              # execute the SQL statement             $stmt-&gt;execute();              # closes the cursor, enabling the statement to be executed again             $stmt-&gt;closeCursor();              # close &amp; unset the connection             $stmt = null;             unset($stmt);             $conn = null;             unset($conn);              # Set success value to TRUE             $my_success = 1;             $my_message = 'success';          } catch(PDOException $e) {              # Append error message to error log          }          return array($my_success, $my_message);      }
213
Why can't I pass the table name to a prepared PDO statement?  $stmt = $dbh-&gt;prepare('SELECT * FROM :table WHERE 1'); if ($stmt-&gt;execute(array(':table' =&gt; 'users'))) {     var_dump($stmt-&gt;fetchAll()); }   Is there another safe way to insert a table name into a SQL query? With safe, I mean that I don't want to do  $sql = "SELECT * FROM $table WHERE 1"
55771
Anyone know how to combine PHP prepared statements with LIKE? i.e.   "SELECT * FROM table WHERE name LIKE %?%";
117512
I am trying to run a SQL code (I use MySQL). What I want to do is to set DATE_REACT a value when the first condition is satisfied, and a different value when the first condition is not satisfied. The problem is that the first condition is executed always, even when it shouldn't. What I am doing wrong?  UPDATE BILLS SET STATE = 2, DATE_REACT = CASE  WHEN (DATE_EM &lt;= CURTIME() &lt;= ADDTIME(DATE_EM, '00:02:00')) THEN ADDTIME(CURTIME(), '00:13:00') ELSE ADDTIME(DATE_REACT, '00:13:00') END WHERE STATE = 1;
31743
I am converting some module from d5 to d7, and in old code there is an update query like this:  &lt;?php db_query("UPDATE {a_table} SET count = count + 1 WHERE field1 = %d", $some_value);   As we see, it is incrementing a field value (AFAIK it is a mysql feature). But, if I use new style of update it won't work.  &lt;?php db_update('a_table')     -&gt;fields(array('count' =&gt; 'count + 1',))     -&gt;condition('field1', $some_value)     -&gt;execute();   Are there any workaround within new style of db update without running an extra query to get existing field value to increment it?
27168
The question is a fairly open one. I've been using Stored Procs with MS SQLServer for some time with classic ASP and ASP.net and love them, lots.  I have a small hobby project I'm working on and for various reasons have gone the LAMP route. Any hints/tricks/traps or good starting points to get into using stored procedures with MySQL and PHP5? My version of MySQL supports Stored Procedures.
39135
I have a site at hostgator, using their "Business account". It's cheap, and comes with a private SSL cert. HOWEVER, their MySQL settings are such that any db connection goes stale fairly quickly, and then the hostgator staff try to convince you to use a VPS or dedicated server at multiple times the expense.  I'm creating and deploying WebAPIs, which can have a 'conversation' which exceeds 60 seconds. When this occurs, the db connection handle is stale, causing any db operations after my timeout to fail.  I'm in the process of implementing the following solution, but am curious if anyone else has solved this issue (where they can not modify their my.ini or my.cnf to give MySQL more resources) in a similar or completely different manner.  last part of a hook_update:  $ret = db_query( "UPDATE {my_table} set field1 = '%s', field2 = %d ... WHERE vid - %d", $node-&gt;field1, $node-&gt;field2, ... $node-&gt;vid ); if (!$ret) {        // get private storage directory for this node:        $cexStorage    = _cex_front_getCexStore();        $nodeFilesPath = DRUPALROOT.'/'.$cexStorage.'/'.$node-&gt;storeDir;        $fname         = $nodeFilesPath.'/recover.node';         $readyToWrite  = serialize( $node );        file_put_contents( $fname, $readyToWrite );        if (file_exists( $fname )) {          _dbgReport( 'seems to have worked!' );        }     }   Then this is called in my hook_form, right after I determine that node->nid is valid:  function _cex_front_recover_node( &amp;$node ) {   if (!isset($node-&gt;storeDir)) {      _dbgReport( '**************************** _cex_front_recover_node: no storeDir!!!' );      return;   }    // get private storage directory for this node:   $cexStorage    = _cex_front_getCexStore();   $nodeFilesPath = DRUPALROOT.'/'.$cexStorage.'/'.$node-&gt;storeDir;   $fname         = $nodeFilesPath.'/recover.node';   if (file_exists( $fname )) {     $recovered = file_get_contents( $fname );     if ($recovered) {       // no longer needed:       unlink( $fname );       $recoveredNode = unserialize( $recovered );       // restore the key values I need to proceed with this node:       $node-&gt;stage = $recoveredNode-&gt;stage;       $node-&gt;rnid  = $recoveredNode-&gt;rnid;       $node-&gt;iris  = $recoveredNode-&gt;iris;       cex_front_update( $node );     }     else {       _dbgReport( '********* failed to read "'.$fname.'"' );     }   } }   I'll be pairing the logic down to only store the key fields I need, but this works! Sure beats upgrading my server at 3x the expense. Anyone else doing something like this?
1754
Basically, one of the greatest questions of all time: What are some ways you're using settings.php in your development/staging workflow?  Right now, I have my settings.php file set up like the following, and I base my development on the server's $HOST directive—meaning I can work on dev.example.com for the development (shared) server, local.example.com for my local computer (and other dev's local code checkouts), and www.example.com (or just example.com) for the live site.  (This code is in the 'Database settings' section of settings.php):       $host = $_SERVER['HTTP_HOST']; $base_url = 'http://'.$host; $cookie_domain = $host;  switch($host) {   case 'example.com': # Production server     $db_url = 'mysqli://prod_sql_user:password@127.0.0.1/prod_db';     $update_free_access = FALSE;     $conf = array (       // Set production config options here...       'example_setting' =&gt; 0,     );     break;    case 'dev.example.com': # Development server     $db_url = 'mysqli://dev_sql_user:password@127.0.0.1/dev_db';     $update_free_access = FALSE;     $conf = array (       // Set production config options here...       'example_setting' =&gt; 0,     );     break;    case 'local.example.com': # Local server     $db_url = 'mysqli://local_sql_user:password@127.0.0.1/local_db';     $update_free_access = FALSE;     $conf = array (       // Set production config options here...       'example_setting' =&gt; 0,       // Turn off most core caching.       'cache_inc' =&gt; 'includes/cache.inc',       'cache' =&gt; CACHE_DISABLED,     );     break;  } ?&gt;   This works pretty well for most purposes, but it means we have a lot of extraneous code sitting around in our shared settings.php file... is there a better way?
44395
I have an application written in PHP with Laravel that regularly prepares and executes statements like this:     -- All parameters are varchar(10)    SELECT c1, c2, c3, c4       from MyBigTable      where is_active = 1        and c1 in (@P1, @P2, @P3, @P4 ... @P250)        AND c2 is not NULL   Users have a big data grid, and they can select many rows (there is even a button to select 'ALL'). If they select 250 rows, this statement is what happens. But it takes more than one minute to run, which is unacceptable.  Table MyBigTable has about 10 millions rows. The estimated execution plan shows that 100% of time is spent on an "index seek, non-clustered". From this I deduce that the situation can not be improved using indexes, and that the only issue is in the use of prepared statements. (If you think I am wrong, just let me know). Moreover, I understand that these prepared statements are prepared, used once and discarded, so I don't think they are really beneficial.  What recommendation should I give to the developers?  Should I just tell them to stop using prepared statements, and hardcode the 250 values in the query?  Or should I give them some workaround, like the use of temporary tables (make a temporary table, insert 250 values, then make a query on MyBigTable JOINed with temp)?  Or any other idea?  EDIT: execution plan
112390
Is there a list describing all of the data_type parameters you can use in PDOStatement::bindParam() ? If none, what do you commonly use, and for what type of field ?  According to PHP manual: data_type Explicit data type for the parameter using the PDO::PARAM_* constants.   I know about the PDO::PARAM_INT and PDO::PARAM_STR . I've heard of PDO::PARAM_LOB but I wasn't sure how and when to use that, though. (for date ?)
69918
So I have an SQL dump file that needs to be loaded using mysql_query(). Unfortunately, it's not possible to execute multiple queries with it.  -> It cannot be assumed that the mysql command-line client (mysql --help) is installed -- for loading the SQL file directly  -> It cannot be assumed that the mysqli extension is installed  /* contents of dump.sql, including comments */ DELETE FROM t3 WHERE body = 'some text; with semicolons; scattered; throughout'; DELETE FROM t2 WHERE name = 'hello'; DELETE FROM t1 WHERE id = 1;   The explode() below won't work because some of the dump content's values contain semicolons.  $sql = explode(';', file_get_contents('dump.sql')); foreach ($sql as $key =&gt; $val) {     mysql_query($val); }   What's the best way to load the SQL without modifying the dump file?
62824
if want querying the following table :  EventDateTime (DateTime), Host (guid), Flag ( bit) 2013-04-01 05:00, {id-x}, 0 2013-04-01 05:01, {id-x}, 0 2013-04-01 05:02, {id-x}, 0 2013-04-01 05:03, {id-x}, 0 2013-04-01 05:04, {id-x}, 1 2013-04-01 05:05, {id-x}, 1 2013-04-01 05:06, {id-x}, 1 2013-04-01 05:07, {id-x}, 0 2013-04-01 05:08, {id-x}, 0 2013-04-01 05:09, {id-x}, 0 2013-04-01 05:10, {id-x}, 0 2013-04-01 05:11, {id-x}, 1 2013-04-01 05:12, {id-x}, 1 2013-04-01 05:13, {id-x}, 1 2013-04-01 05:14, {id-x}, 1 2013-04-01 05:15, {id-x}, 0   and the result should be:  2013-04-01 05:04, {id-x}, 1 2013-04-01 05:07, {id-x}, 0 2013-04-01 05:11, {id-x}, 1 2013-04-01 05:15, {id-x}, 0   i have try it with LAG,LEAD and OVER Clause. but no luck :(
43952
Which is the equivalent of mysql_data_seek using pdo objects? Can you give me an example?  Thanks!
101940
I just installed appache, MySQL and PHP, suring my installation, I was prompted for a MySQL root user password, but when I start my MySQL, it pops the error, and I just can't seem to find a way around it, most solutions I've read doesn't solve it, moreover, I found out that the /var/run/mysqld path doesn't even exist, my php also needs the socket to connect to my Database.
42660
I am testing my application I need some code that stable simulates the deadlock on database site (sql script if possible).  Thank you.  ADDED:
108487
I have the code below (I've included what I believe are all relevant sections):  private String readCommand = "SELECT LEVEL FROM USERS WHERE VAL_1 = ? AND VAL_@ = ?;"; public bool read(string id) {     level = -1;     MySqlCommand m = new MySqlCommand(readCommand);     m.Parameters.Add(new MySqlParameter("", val1));     m.Parameters.Add(new MySqlParameter("", val2));     MySqlDataReader r = m.ExecuteReader();     if (r.HasRows)         level = Convert.ToInt32(r.GetValue(0).ToString());     r.Close();     return true; }   When I run this, I get an IndexOutOfBoundsException on adding the first parameter. What have I done wrong?
21568
$sql = "SELECT Kill FROM tbl_pvporderview";   Problem is that I end up with: Incorrect syntax near the keyword 'Kill'.  Because kill is a T-SQL command... any way to bypass it?  I can't change the column name because it's used by the software a lot and I cant change the software that's using the database.  So it simply fails if I use sqlserv to select data from that column. '' or "" wont help.  The complete statement would be:  $sql = "SELECT serial,Kill FROM tbl_pvporderview WHERE Kill &gt; (?) ORDER BY Kill DESC ";
106704
Which is the valid syntax of this query in MySQL?  SELECT * FROM courses WHERE (now() + 2 hours) &gt; start_time   note: start_time is a field of courses table