As others have suggested, I would also recommend combining your multiple selects into a single JOIN statement. As others have also said, I would recommend using mysqli or PDO. Both of these will effectively help eliminate SQL injections as well as prevent you from using code that has been deprecated and that will be removed in the next release.
Here is an example on how you can rewrite your query and loop logic (example uses PDO as it has been a while since I used mysqli). I think I may have missed part of the join, as I'm not 100% sure what the association is between your tables.
$sql = "
SELECT
Description,
WebDesc
FROM T_AdditionalInfo info JOIN T_Accessories acc ON info.ItemNo = acc.PartNo
WHERE info.Description LIKE :title
";
$db = false;
$table_date = '';
try {
$db = new PDO(...);
}
catch(PDOException $e) {
$db = false
//Failed to connect to DB for some reason
}
if($db !== false) { //Did we connect?
if(($stmt = $db->prepare($sql)) !== false) { //Prepare the statement
$stmt->bindParam(':title', '%abc%'); //Bind the parameter
if($stmt->execute() !== false) { //Execute the statement
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { //Loop through the result set
$table_w_spaces = $row['Description']; //Get title
$title = str_replace(' ', '-', $title_w_spaces); //alter title
$table_date .= "
<div>
<h6>{$table_w_spaces}</h6>
<img src="/images/{$title}.jpg" alt="{$title}" title="{$title}" class="alignnone size-full" width="114" />
<p>{$row['WebDesc']}</p>
</div>
";
}
}
else {
//Execution of statement failed
print_r($stmt->errorInfo());
}
}
else {
//An error occurred when preparing SQL statement, probably a SQL error
print_r($stmt->errorInfo());
}
}
This might be me getting a little ahead of myself, but I'm guessing you will probably be doing things like this a lot. Once you become familiar with PDO or mysqli, it might be beneficial to create a class to take away a lot of the repetitive tasks. Also if you should ever change to another DB server (from MySql, to PostGre, or MSSQL) or even change your server name, it is all right there in one spot and easy to change. If the example class below seems a bit confusing at first don't worry. See the examples below and it should become clear. It is basically a way of taking away all of the repetitive tasks and integrating them into two specific functions executeQuery and executeNonQuery.
class Database {
private $db = false;
private $connected = false;
public function __construct($db = 'DefaultDBName', $username = 'MyUser', $password = 'MyPassword') {
$this->connect($db, $username, $password); //Auto connect when you create a new database
}
/**
* Connect to a database
*
* @param $db The database to connect to; default is DefaultDBName
* @param $username The username to connect as; default is MyUser
* @param $password The password to use when connecting; default is MyPassword
* @return True/False if the connection was successfull
*/
public function connect($db = 'DefaultDBName', $username = 'MyUser', $password = 'MyPassword') {
try {
$this->db = new PDO('drivername:Server=MYDBSERVER;Database=' . $db, $username, $password); //Create new connection
$this->connected = true; //We are connected
}
catch(PDOException $e) { //Oh no. An error
$this->db = false; //Set DB back to false, just in case
$this->connected = false; //We couldn't connect
//You can do something with the error message if you wish
}
return $this->connected
}
/**
* Prepares a SQL statement for execution
*
* @param $sql The SQL string to be prepared
* @params $params An optional array of parameters to bind to the statement
* @return The prepared statement; false if failed
*/
public function prepare($sql, $params = array()) {
$stmt = false;
if($this->connected) { //Are we connected
if(($stmt = $this->db->prepare($sql)) !== false) { //Prepare the statement
foreach($params as $param => $value) { //Bind the parameters
$stmt->bindValue($param, $value);
}
}
else {
//You can do something with $stmt->errorInfo();
}
}
return $stmt;
}
/**
* Execute a prepared statement
*
* @param $stmt A PDO statement
* @param $params An optional array of parameter values
* @return True/False
*/
public function execute($stmt, $params = array()) {
if($this->connected) { //Are we connected
if(!empty($params)) { //Can't bind an empty array of parameters
$result = $stmt->execute($params); //Execute with parameters
}
else {
$result = $stmt->execute(); //Execute without parameters
}
}
return $result;
}
/**
* Gets the results from an executed statement
*
* @param $stmt A reference to a PDO statement
* @return An array of results from the statement
*/
public function getResults($stmt) {
$results = array();
if($stmt !== false) { //Make sure we have a valid statement
while($row = $stmt->fetch(PDO::FETCH_ASSOC))) { //Get all of the data for the row
$results[] = $row; //Add the row to the results array
}
}
return $results; //Return the results
}
/**
* Executes a query and returns the results
*
* @param $sql The SQL query
* @param $parameters An array of parameters
* @return An array of results or false if execution failed
*/
public function executeQuery($sql, $params = array()) {
$results = false;
if($this->connected) { //Are we connected
if(($stmt = $this->prepare($sql, $params)) !== false) { //Prepare the statement
if($this->execute($stmt) !== false) { //Execute the statement
$results = $this->getResults($stmt); //Get the result set
}
}
}
return $results;
}
/**
* Executes a query, but returns no results
*
* @param $sql The SQL query
* @param $parameters An optional array of paraters
* @return True/False
*/
public function executeNonQuery($sql, $params = array()) {
$success = false;
if($this->connected) { //Are we connected
if(($stmt = $this->prepare($sql, $params)) !== false) { //Prepare the statement
if($this->execute($stmt) !== false) { //Execute the statement
$success = true; //Successfully executed
}
}
}
return $success;
}
}
EXAMPLES
In each of the examples, assume the following
$sql = "
SELECT
Description,
WebDesc
FROM T_AdditionalInfo info JOIN T_Accessories acc ON info.ItemNo = acc.PartNo
WHERE info.Description LIKE :title
";
$parameters = array(':title' => '%abc%');
1) Putting it all together using the new database class
$db = new Database();
if(($stmt = $db->prepare($sql, $parameters)) !== false) {
if($db->execute($stmt)) {
$results = $db->getResults($stmt);
foreach($results as $result) {
//Do something with this result
}
}
}
2) Now I know I said that creating this class would make everything easier by taking away all of the repetitiveness of preparing the statement, binding parameters, executing the statement, and then retrieving the result set. Below is a quick example of how all of the above can be done in one line.
$db = new Database();
if(($results = $db->executeQuery($sql, $parameters)) !== false) {
foreach($results as $result) {
//Do something with this result
}
}
3) But what about the queries I want to use that don't return a result set? Can I still quickly execute a statement without all of the mess? Yes. You can use the executeNonQuery function. Below is an example of that.
$db = new Database();
$sql = 'update table1 set field = :value where id = :id';
$parameters = array(':value' => 'val1', ':id' => '5');
if($db->executeNonQuery($sql, $parameters)) {
//Yay! The update was successfull
}
UPDATE
Per my conversation with the OP (see comments), here is an updated version of his query that should get the desired results. The LIMIT 1 portion of the query ensures that only one ItemNo is used which should give only the 8 accessories being looked for rather than all 32 (8 accessories X 4 ItemNo's).
SELECT
info.Description,
acc.WebDesc
FROM T_AdditionalInfo info JOIN T_Accessories acc ON info.ItemNo = acc.PartNo
WHERE info.ItemNo = (SELECT ItemNo FROM T_AdditionalInfo WHERE Description LIKE %abc% LIMIT 1)
The nested select statement is called a subquery. Since you indicated you are using MySQL, here is a reference page that you might find useful. Subqueries are very useful in SQL for things like this as well as using IN clauses SELECT * FROM t1 WHERE id IN (SELECT t1_id FROM t2).
