CODEWITHSUNDEEP
phpsql
Gurnor
Gurnor

Reputation: 339

SQL query overused in PHP files?

I have SQL query which goes to result table:

$php_variable_answer = "SELECT 
                        answer_id 
                        FROM result 
                        WHERE question_id = $php_variable_question";

than query goes to answer table and get answer:

$php_variable_answer = "SELECT answer 
                        FROM answer_table
                        WHERE answer_id = $php_variable_answer";

I have 20 php files on my website. I am using these three query each time to get answer in at least 5 or 8 files.

My questions:

Upvotes: 1

Views: 109

Answers (3)

SeanCannon
SeanCannon

Reputation: 77976

To keep things simple, you could always just create a helper file which contains functions you'd utilize for these queries. Example: 

<?php 
/* qa_helper.php */

function get_question($q_id){
  // connect to db...
  return mysql_result(mysql_query('SELECT answer_id from result where question_id = ' . $q_id),0);
}

function get_answer($a_id){
  // connect to db...
  return mysql_result(mysql_query('SELECT answer from answer_table where answer_id = ' . $a_id),0);
}

Then in your files, you simply reference it: 

<?php
include_once('qa_helper.php');

$answer = get_answer(123);
}

This is way old-school procedural PHP. I recommend you look into an Object-oriented MVC framework to organize your code. Something simple like CodeIgniter.

Upvotes: -1

Mez
Mez

Reputation: 24943

It'd probably be best to put this into a function somewhere (and then include that function where you need it)

Your SQL could easily not require 2 queries too..

SELECT at.answer FROM result AS r 
LEFT JOIN answer AS at ON (at.answer_id = r.answer_id)
WHERE r.question_id = $php_variable_question

If you know the question_id's in advance, you could do something like

SELECT at.answer FROM result AS r 
LEFT JOIN answer AS at ON (at.answer_id = r.answer_id)
WHERE r.question_id IN (1,2,3);

Upvotes: 0

Matt Stein
Matt Stein

Reputation: 3053

The short answer is that re-using code is a great idea, and the more you can effectively reuse stuff the better. (Easier to make updates, keep things consistent, etc.) If you're reusing the same queries or the same PHP, move the reusable parts to an appropriately-named file, like database.php. Then, add this line to the top of any file that needs your query functions:

require_once('database.php');

This will make your existing functions and queries available, so you don't have to re-write them in each new PHP file.

If your PHP files start growing, you might want to start researching the benefits of using a framework to keep things organized and efficient. A framework is a code base you can use that'll encourage you to keep organized and give you some existing code to build from. A PHP framework I like using is CodeIgniter, and there are many others available.

Upvotes: 3

Related Questions