Using an array in a query replacing the columns with an array

blackbox

Hi all.

I am trying to construct a query that uses the content of an array in place of the seclected columns and in the WHERE clause.

I have the Array and I know that it contains the correct data.

My query looks like this:

$strSQLCol = "SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'nxtable'
        AND table_schema = 'nx_db'
        AND column_name LIKE 'SeqID%'";
$SeqID = mysqli_query($link,$strSQLCol);

do {
	$List[] = $row;
} while($row = $SeqID->fetch_assoc());

$column = array();	
	foreach($List as $key => $value) {
    $column[] = $value['COLUMN_NAME'].'<br />';
}

$strSQL = "SELECT " . join(', ', $column ) . "  nxtable WHERE '1' IN ('" . join(' ', $column) . "')";
$SeqHead = mysqli_query($link,$strSQL);
echo $strSQL;
do {
	$List2[] = $row;
} while($row = $SeqHead->fetch_assoc());

It's the last query I am having issues with. If I echo $strSQL the ARRAY data is there but the column seperator "," is before the column name where as we know it should be after the column name.

As you can see I am use " . join(', ', $column ) . " to insert my columns, using that how can I mave the comma from before to after the column name.

Any help would be great. Thank you in advance for your time.

Blackbox

Weedpacket

You do know that

WHERE '1' IN ('" . join(' ', $column) . "')"

will only succeed if one of the columns in that table really is named "1", right?

NogDog

If it's a list of strings you need for the IN() clause, then you'll need to quote each one:

" . . . IN('" . join("', '", $column) . "') . . . ";

pbismad

The reason you are seemingly getting a ',' join/implode/separator before the first column name is because that isn't the first piece of data in the $column array. Your do/while loop is storing a null value as the first piece of data in the array. If you had php's error_reporting set to E_ALL and display_errors set to ON, you would be getting an undefined variable error message, for $row, alerting you to the problem.

Do/while loops are almost never used (unless you are using dreamweaver and the needless lines of code it produces to accomplish each task.) If you use a more common while(){} loop, the data you store in the $column array will be what you expect. You should also define/initialize the $column array to an empty array before you use it.

At the end of your last thread, you stated you redesigned your tables, queries, and code as suggested. What happened, this thread is back to a database table with a bunch of seqIDn columns, requiring a extra code and queries to even find the data that's stored in any row?

blackbox

Hi all,

Before I move forward could someone explain which method is best for my table design. I have changed the design of all my tables as Pbismad said I should and they do work better.

The last tables holds the history of a complete room audit.

Each audit refers to one room in a hotel and the room has multiple items to audit.

A audit record would have the following example;

RoomNo
Audit date
Auditor
SeqIDxx (the item ID, which there are many)

If the audit item passes the SeqIDxx is set to 1, if it fails it is set to 2.

The way I see it is, if I have a record that holds all the audit items in one row I would then have x number of rows, one for each room.

If I design this table to store each audit item as a seperate record I would have x number of rows, one for each audit item which would be the number of audit items (46) times the number of rooms (869) which would create 39974 records for one audit.

Which is better, one record for each audit or 39974 records for each audit.

Now I know I could create tables, one for each audit item but then I would have one table (lets call it tblRoom) holding the room No and audit date and so on, and 46 tables for the item result, linking all 46 tables to tblRoom using a common column.

This is a college project and i would like to get my head around the best practice in designing the table structures.

Again, many thanks for your time.

pbismad

This design process is called database normalization (there is much information that can be found on the web.) Basically, unique data is stored only once and repetitive data, that's related to each unique piece of data, is stored as row(s) in a different table.

You need to (and probably do) have a table that defines the audit items. This would assign an id to each audit item. You would store this audit_item_id as data in an audit_result table.

You need to store just data that exists. You would store data identifying the exception, rather than the norm. Assuming that most of the items typically do pass, you would only store data for things that don't pass.

Next, you would have your existing audit table, only holding the unique information about each audit - id, date, auditor_id, room number. This will assign a unique id to each audit.

The audit_result table would hold row(s) with the audit_id and failed audit_item_id. If a room had no failed items for an audit, there would be no row(s) for it in this table.

Also, by storing these each as a separate row, you can now have columns tracking the status of the failed item, who corrected the item, what was done to correct the item, and when it was corrected, ...

blackbox

Hi Pbismad,

Could you please take a look at the following and let me know if I am now designing my structure corrently.

Below is an example query and I have also attached an image of the structure and the result

SELECT
Tbl_Client.HotelID,
Tbl_Client.AuditorID,
Tbl_History.UniqueID,
Tbl_History.Room,
Tbl_Category.SeqHeadTitle,
Tbl_AuditItems.SeqItemID,
Tbl_AuditItems.SeqItemText,
Tbl_History.StartDate,
Tbl_History.CompleteDate
FROM
Tbl_Client
Right Join Tbl_History ON Tbl_Client.HotelID = Tbl_History.HotelID
Right Join Tbl_Category ON Tbl_History.SeqID = Tbl_Category.SeqHeadID
Right Join Tbl_AuditItems ON Tbl_Category.SeqHeadID = Tbl_AuditItems.SeqHeadID
WHERE Tbl_AuditItems.SeqItemID = '203'

[ATTACH]5367[/ATTACH]
[ATTACH]5371[/ATTACH]

Again, many thanks for your time.

Blackbox

Query_image.png query_result.png