Fetch rows from MySQL - Could be done simpler?

Question

I am using ADODB for connecting with MySQL and i was tired of copying loops for every SQL query so i have created some function:

function SQLselect($prefix, $tabela, $warunek){
    $db = NewADOConnection('mysqli');
    $db -> Connect("localhost", "root", "", "dbname");      

    $rowsname = $db->Execute("SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='dbname' AND `TABLE_NAME`='{$tabela}';");

    while (!$rowsname->EOF)
    {
        $nameofrows[] = $rowsname -> fields['COLUMN_NAME'];
        $rowsname->MoveNext();
    }
    $rowsname->Close();

    $sql = $db->Execute("Select * from `{$tabela}`");

    while($zadanie = $sql->FetchRow()){
       for($i=0; $i < count($nameofrows); $i++){
           global  ${$prefix.$nameofrows[$i]};
           ${$prefix.$nameofrows[$i]}[] = $zadanie["$nameofrows[$i]"];
       }
    }
}

And it works fine. Fetch all rows to vars named like rows in table with prefix.

But I am pretty sure that could be done simpler, but I cannot find a right way.

My main suggestion would be to stop using ADODB, unless you have a specific reason to be doing so. PDO imo is a better option. — Jonnix, Jul 06 '16 at 13:18
@ErwinMoller ADODB is slower, and not a standard library (compared to PDO) so when porting your code to another host, you'll need to add ADODB in as well — iamgory, Jul 06 '16 at 13:20
Besides the fact that your code is horribly vulnerable to SQL injections: use an ORM. It might be tricky to set up but querying and updating becomes a breeze. — marstato, Jul 06 '16 at 13:23
And creating all those global variables is a nasty habit as well. You get returned an array, use that — RiggsFolly, Jul 06 '16 at 13:24
@JonStirling: I think AdoDB deserves better than that. :-) Adodb is much more than PDO. It is a full database abstraction layer, where PDO is only a lightweight wrapper around some functions, and yes, thus faster. Try to create a database with PDO, you cannot. Concerning 'standard library', I don't care personally about that. AdoDB is just another lib to copy too. Full PHP. Nothing to install. — Erwin Moller, Jul 06 '16 at 13:30
@ErwinMoller Seems creating a DB in PDO is simple enough http://stackoverflow.com/questions/2583707/can-i-create-a-database-using-pdo-in-php — Anigel, Jul 06 '16 at 13:43
@Anigel: That example shows how to create a mysql database. Adodb is an abstaction layer, so it uses vendor-agnostic syntax to create a database. (I am not saying that is extremely useful, just saying a database abstraction layer is something else than PDO.) — Erwin Moller, Jul 06 '16 at 14:05
@ErwinMoller Come on... I'm not the one that's been replying to you... — Jonnix, Jul 06 '16 at 14:06
@ErwinMoller As a side note, PDO is an abstraction layer. It is DB agnostic. That you can put DBMS specific SQL in makes no difference, and is the same for ADODB. It is not simply a wrapper around other functions. — Jonnix, Jul 06 '16 at 14:10
@JonStirling: No. That is incorrect. When you simply execute some command, and that commands happens to be a CREATE TABLE like construct, PDO will happily diliver the command to the database. But that is completely something else than having a *general syntax* like Adodb has, to create tables. The latter is vendor-agnostic. The former isn't. — Erwin Moller, Jul 06 '16 at 14:13
@ErwinMoller You mean the general syntax that isn't anywhere in the question? I'm not saying you're wrong about ADODB, I'm saying you're wrong about PDO. PDO is a DBAL. Simple as. ADODB appears to do the same, and actually adds another layer ON TOP of that, which PDO doesn't have. But that doesn't make PDO any less an abstraction layer. And since OP doesn't appear to use any of this additional abstraction, PDO appears even more applicable over ADODB. Again, imo. — Jonnix, Jul 06 '16 at 14:14
@JonStirling: I don't think you understand what I am writing. I blame my English. Read up here to see what I mean by general syntax to create table/etc on different databases. The programmer doesn't even have to know if the underlying database is MySQL/Postgres/etc to use it. http://phplens.com/lens/adodb/docs-datadict.htm PDO only abstracts a smaller amount of the database interaction away. — Erwin Moller, Jul 06 '16 at 14:19
@ErwinMoller I think I understand exactly what you are saying (your English seems pretty good to me), but I wonder if you are understanding what I'm trying to get across, Simply, PDO IS a DBAL, it IS DB agnostic, and it's NOT a function wrapper. That is all. As I said in my last comment, you're right that ADODB has an additional abstraction layer that PDO doesn't have, but that's beside what I'm trying to correct from your previous comments :P. — Jonnix, Jul 06 '16 at 14:27
Well ADODB is my personal choice, because it's more comfortable for me. I have been working a few years with that library. I had used PDO once - it's ok, as easy as ADODB but I can't convince to that :) For security I am using another function (not included yet in this function) that protect from the SQL injections. This was an experimental function for facilitate work. Because now I have to list all vars, assign to them data from DB and later assign those vars to smarty. (which I am using as template engine) — Moskier, Jul 07 '16 at 06:25
@ErwinMoller Which seems a perfectly valid response considering the question we are all responding to refers specifically to mysql and not A.N.Other DB — Anigel, Jul 07 '16 at 12:37

Fetch rows from MySQL - Could be done simpler?

0 Answers0