Operation for selecting an internal join

Question

SQL statement

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

leads to 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 scratch my head several times, with all the examples here in Stack Overflow, but I don’t see to find something close enough, which is understandable. I hope someone can explain it simply and simply and can provide an example of how I can create an HTML table with 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 if the query returns a result with is_array and count (), and then iterates through the array using the / case switch to determine which label to use. Just add a case from 2 to 8 and this should do the trick.

EDIT: An alternative would be to use a variable in the HTML repository and repeat it after the process is complete.

Answer 2

This nasty bit of code should do the trick, it uses group_concat to group nonzero rows into a single nd field, since unions mean that 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 iterate through the lines well and do this:

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

And so on.

I also wrote a Q & A length that may interest you here: How an SQL query can return data from multiple tables

Edit: I see that the other two people who responded told you to redesign the table, which I kind of do, but seemingly disagree.

This table is firmly normalized , which means that you can add a hundred additional form fields and not touch the table itself - this is (surprisingly) a good thing. I would say that this design makes it difficult for you to obtain this data, but it is very reliable - if not surprisingly quick or easy access.

Answer 3

The problem is the database design, not 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 | 

Here you can call them simply using the SQL statement:

 SELECT * FROM UserTable WHERE CUST_ID = 6 

This will return you a simple answer that you can use like this:

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

(Disclaimer: I have limited knowledge of PHP, but I think it will work. If not, I know that it is not much more difficult)

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

I suggest you read Database Normalization for a better understanding of how databases should be created. But this is not necessary for this, just something good to know and understand.