How to write these statements efficiently?

Question

I have these three statements that I repeat in my code for each id or variable.

$firstname_sql = $db->sql_query('SELECT column FROM table WHERE id = 1');
$lastname_sql = $db->sql_query('SELECT column FROM table WHERE id = 2');
etc..

$firstname_row = $db->sql_fetchrow($firstname_sql);
$lastname_row = $db->sql_fetchrow($lastname_sql);
etc..

$template->assign_var('FIRSTNAME', $firstname_row['variables'];
$template->assign_var('LASTNAME', $lastname_row['variables'];
etc..

What would be a more efficient way of writing these?

I've tried selecting the column count and use that in a for loop for the id's but it ends up being as much if not more code as I have now.

Also, I use sql_query to query the database for the column and it returns an object. Then I have to do sql_fetchrow to get the actual value. Is there a way to do this in one go/line?

I use one table for this and it looks like this:

module_id, int, primarykey
form_id, int, primarykey
form_name, varchar
active, int

The column I'm selecting is active and the id is the form_id. I'm using it to see which forms are active and which are not.

`SELECT id, column FROM table WHERE id IN (1, 2)` would be a single query. — h2ooooooo, Mar 07 '16 at 14:48
Just so I understand, you actually store each value (first name, last name etc.) in a different row with each their own id? — therebelcoder, Mar 07 '16 at 14:58
@stevenca Firstname or lastname are a type of form. I'm not storing the value for firstname or lastname in this table. Just the form_id, i.e. first name has form_id 1 and whether it's active 1 or not 0. — Kenan, Mar 07 '16 at 14:59

score 0 · Answer 1 · answered Mar 07 '16 at 15:03

There is a way to execute both queries as one, and get one object back instead of two. You should use paramatized queries, but for a Proof Of Concept I'll use your queries as a base

$query = $db->sql_query('SELECT table1.column AS firstName, table2.column AS secondName
    FROM table table1, table table2
    WHERE table1.id = 1
    AND table2.id = 2');

$names = $db->sql_fetchrow($query);
$template->assign_var('FIRSTNAME', $names['firstName'];
$template->assign_var('LASTNAME', $names['lastName'];

score 0 · Answer 2 · answered Mar 07 '16 at 15:14

Since both values are in the same table, stored as entities, I would use the following trick: (one statement)

SELECT max(x.first) AS firstname, max(x.last) AS lastname FROM ( SELECT column AS first, null AS last FROM table WHERE id = 1 Union all SELECT null AS first, column AS last FROM table WHERE id = 2 ) x

I wrote this from head, so didn't test it. But it should work. The result will be one neat row with firstname and lastname as separate fields in that row.

score 0 · Accepted Answer · answered Mar 07 '16 at 17:22

0

Alright so this is how I've done it eventually.

I created a function with id and placeholder (template variable) parameters.

function activeForm($id, $placeholder) {
    $active = $db->sql_fetchrow($db->sql_query('SELECT column FROM table WHERE id = ' . $id));
    $template->assign_var($placeholder, $column['column'];
}

Then I just call the function

activeForm(1, 'FIRSTNAME');

Simple as that.

answered Mar 07 '16 at 17:22

Kenan

114
9

But now you are still executing 1 statement for 1 variable? Thought your question was: How to write the statement efficiently!? – therebelcoder Mar 08 '16 at 17:28

score -1 · Answer 4 · edited May 23 '17 at 12:07

From your question I'm guessing youa re thinking of prepared statements or - how to properly parametrize your queries.

To establish a connection to MySQL Using PDO :

$dbh = new PDO('mysql:host='.HOST.';dbname='.DATABASE,USERNAME,PASSWORD);

that's it, the connection is established and you could reuse $dbh for performing queries for example to fetch the result from a table user you just need two line of code.

$sth = $dbh->query('SELECT id,name,email FROM users');
$user = $sth->fetch(PDO::FETCH_ASSOC);

Now $user will have all the values fetched as an associative array.

To Insert value into the database you need to do the following.

$sth = $dbh->prepare('INSERT INTO users(name,email) VALUES(:name, :email)');
$sth->bindParam(':name', 'My Name');
$sth->bindParam(':email', 'email@email.com');
$sth->execute();

The above code is using named placeholder, this way PDO will keep you safe from many vulnerabilities as it will keep you away from MySQL Injection. to get you started have a look at this tutorial by netttus, they have explained it very nicely, this article will explain all your dilemmas regarding PDO

Reference: PHP PDO MySQL Queries

Not the downvoter, but this doesn't answer the question. 1) you don't know if OP's using some DB class with PDo already or not; 2) You could at the very least provide the solution with your method, not giving a random example of usage — Damien Pirsy, Mar 07 '16 at 15:08

How to write these statements efficiently?

4 Answers4