A community in which webmasters can ask for help with topics such as PHP coding , MySQL , IT jobs, web design, IT security.
Current location:homephp forumphp talk in 2009 yearSELECT * FROM in MySQLi - page 1
User InfoPosts
SELECT * FROM in MySQLi#1
My site is rather extensive, and I just recently made the switch to PHP5 (call me a late bloomer).

All of my MySQL query(s before were built as such:

"SELECT * FROM tablename WHERE field1 = (value( && field2 = (value2(";


This made it very easy, simple and friendly.

I am now trying to make the switch to mysqli for obvious security reasons, and I am having a hard time figuring out how to implement the same SELECT * FROM queries when the bind_param requires specific arguments.

Is this statement a thing of the past?

If it is, how do I handle a query with tons of columns involved? Do I really need to type them all out every time?

posted date: 2009-04-14 23:28:00


Re: SELECT * FROM in MySQLi#2
I had made out the solution of this problem. click to view my topic...

hope that hepls.

posted date: 2009-04-14 23:28:01


Re: SELECT * FROM in MySQLi#3
You can still use it (mysqli is just another way of communicating with the server, the SQL language itself is expanded, not changed). Prepared statements are safer, though - since you don(t need to go through the trouble of properly escaping your values each time. You can leave them as they were, if you want to but the risk of sql piggybacking is reduced if you switch.

posted date: 2009-04-14 23:33:00


Re: SELECT * FROM in MySQLi#4
Sometimes you even need the old usage. Mysql can't do multiple inserts in a prepared statement for example.

posted date: 2009-04-14 23:36:00


Re: SELECT * FROM in MySQLi#5
it cant? isn't that something that most people need to do?

posted date: 2009-04-14 23:37:00


Re: SELECT * FROM in MySQLi#6
"SELECT * FROM tablename WHERE field1 = (value( && field2 = (value2(";becomes"SELECT * FROM tablename WHERE field1 = ? && field2 = ?";which is passed to the $mysqli::prepare:$stmt = $mysqli->prepare( "SELECT * FROM tablename WHERE field1 = ? && field2 = ?");$stmt->bind_param( "ss", $value, $value2); // "ss( is a format string, each "s" means string$stmt->execute();$stmt->bind_result($col1, $col2);// then fetch and close the statementOP comments:so if i have 5 parameters, i could potentially have "sssis" or something (depending on the types of inputs?) Right, one type specifier per ? parameter in the prepared statement, all of them positional (first specifier applies to first ? which is replaced by first actual parameter (which is the second parameter to bind_param)).mysqli will take care of escaping and quoting (I think).

posted date: 2009-04-14 23:45:00


Re: SELECT * FROM in MySQLi#7
so if i have 5 parameters, i could potentially have "sssis" or something (depending on the types of inputs?)

posted date: 2009-04-14 23:48:00


Re: SELECT * FROM in MySQLi#8
No, it cannot do something like INSERT INTO A (a,b) VALUES (1,2), (3,4), (5,6). And if (and only if) speed is an issue (which shouldn't be in 99% of cases - but we just had one in a recent project), consider this: prepared statements need 3 communications for 1 query (prepare, set var, execute)

posted date: 2009-04-15 00:13:00


Re: SELECT * FROM in MySQLi#9
While you are switching, switch to PDO instead of mysqli, It helps you write database agnositc code and have better features for prepared statements.http://www.php.net/pdoBindparam for PDO:http://se.php.net/manual/en/pdostatement.bindparam.php$sth = $dbh->prepare("SELECT * FROM tablename WHERE field1 = :value1 && field2 = :value2");$sth->bindParam((:value1(, (foo();$sth->bindParam((:value2(, (bar();$sth->execute();or: $sth = $dbh->prepare("SELECT * FROM tablename WHERE field1 = ? && field2 = ?");$sth->bindParam(1, (foo();$sth->bindParam(2, (bar();$sth->execute();or execute with the parameters as an array:$sth = $dbh->prepare("SELECT * FROM tablename WHERE field1 = :value1 && field2 = :value2");$sth->execute(array((:value1( => (foo( , (:value2( => (bar());It will be easier for you if you would like your application to be able to run on different databases in the future.I also think you should invest some time in using some of the classes from Zend Framwework whilst working with PDO. Check out their Zend_Db and more specifically [Zend_Db_Factory][2]. You do not have to use all of the framework or convert your application to the MVC pattern, but using the framework and reading up on it is time well spent.

posted date: 2009-04-15 00:31:00


Re: SELECT * FROM in MySQLi#10
Yeah, search engines don't like querying for "SELECT *" much; I edited my post with a couple links (the first link is especially useful).

posted date: 2009-04-15 01:30:00


Re: SELECT * FROM in MySQLi#11
awesome, iv never heard of PDO, ill check it out

posted date: 2009-04-15 07:32:00


Re: SELECT * FROM in MySQLi#12
oh, i gotcha....makes sense now

posted date: 2009-04-15 07:33:00


select page: « 1 2 »
Copyright ©2008-2017 www.momige.com, all rights reserved.