Inner join select statement

Question

the SQL statement

$CustInfo = Select * from wp_wpsc_submited_form_data where log_id = '6';

results in the following:

ID    CUST_ID    FORM_ID    VALUE

81       6          2       John
82       6          3       Smith
83       6          4       123 Main Street
84       6          5       Houston
96       6          6       NULL
85       6          7       US
86       6          8       77459

I have been scratching my head for a few hours with all the example here on Stack Overflow, but can't see to find anything close enough that is understandable. I am hoping someone can plainly and simply explain and may be provide an example as to how I can generate an HTML table with the values.

example:

Firstname :   <%php echo $CustInfo[2]; ?>
Lastname :    <%php echo $CustInfo[3]; ?>
Address :    <%php echo $CustInfo[4]; ?>

etc...

Answer 1

How about something like this: (untested)

if(is_array($CustInfo) && count($CustInfo) > 0)
{
  echo "<table>";
  for($i=0;$i<count($CustInfo);$i++)
  {
     echo "<tr>";
     switch($i)
     {
       case 1: echo "<td>FirstName:</td><td>".$CustInfo[i]."</td>";
       ...
     }
     echo "</tr>";
  }
  echo "</table>";
}

I check to see if the query returned a result with is_array and count() then iterator through the array using a switch/case to determine what the label to use. Just add case 2 to 8 and this should do the trick.

EDIT: An alternative would be to use a variable to the store the HTML and echo it after the process has completed.

Answer 2

This nasty bit of code should do the trick, it uses a group_concat to group non null rows into a single field nd as the unions mean only one column is null for each row, it should work:

select 
    group_concat(Firstname) as FirstName,
    group_concat(Surname) as Surname,
    group_concat(Add1) as Add1,
    group_concat(Add2) as Add2,
    group_concat(Add3) as Add3,
    group_concat(Country) as Country,
    group_concat(ZipCode) as ZipCode
from
(
    select
        value as FirstName,
        null as Surname,
        null as Add1,
        null as Add2,
        null as Add3,
        null as Country,
        null as ZipCode
    from 
        wp_wpsc_submited_form_data 
    where 
        log_id = '6' // I can't actually see this field in your columns??
        and form_id=2
    union
    select
        null as FirstName,
        Value as Surname,
        null as Add1,
        null as Add2,
        null as Add3,
        null as Country,
        null as ZipCode
    from 
        wp_wpsc_submited_form_data 
    where 
        log_id = '6'
        and form_id=3
    union
    ...
    ...
    ...
    union
    select
        null as FirstName,
        null as Surname,
        null as Add1,
        null as Add2,
        null as Add3,
        null as Country,
        Value as ZipCode
    from 
        wp_wpsc_submited_form_data 
    where 
        log_id = '6'
        and form_id=8
)

Then you should be able to iterate nicely through the rows and do this:

Firstname :   <%php echo $CustInfo['FirstName']; ?>

And so on.

I also wrote a length Q&A you might be interested in here: How can an SQL query return data from multiple tables

Edit: I see that the other two folks who answered told you to redesign your table - which I sort of do, but sort of don't agree with.

This table is ruggedly normalized, which means you can add a hundred extra form fields and not have to touch the table itself - that's a (amazingly) good thing. I would say that this design is making it harder for you to get this particuar data out, but it very robust - if not amazingly quick or easy to access.

Answer 3

The issue is the database design, not the PHP or SQL.

Your table should look like this:

| CUST_ID (PK) |  FIRSTNAME | LASTNAME | STREETADDRESS   | CITY    | STATE | ZIPCODE |
 ------------------------------------------------------------------------------------
| 6            | John       | Smith    | 123 Main Street | Houston | NULL  | 77459   |
| 7            | Jane       | Doe      | 456 Fake St.    | Richmond| VA    | 23860   |

From here, you can call these by simply using the SQL Statement:

SELECT *
FROM UserTable
WHERE CUST_ID = 6

This will return you a simple answer, which you can use like so:

FirstName: <%php echo $CustInfo['FIRSTNAME']; ?>

(Disclaimer: I have limited PHP knowledge, but I think this will work. If not, I do know it's not much more complicated)

Much much simpler, much less code... and most importantly imho, MUCH more efficient.

I do suggest you read up on Database Normalization for better understanding on how Databases should be designed. But it's not needed for this, just something good to know and understand.