Control panel that displays like Webassist Email block. - that joins a lot of tables (23 tables) based on an ID

Can I call you later?

Trying to create a content manager page (for viewing quote requests) that displays like Webassist Email block. - that displays (joins?) a lot of tables (23 tables) based on an ID.

What I would like is:

Select * from TABLE1, TABLE2, TABLE3, TABLE4 etc, WHERE quote_id_person = $valuepassedfromurl

Then display all tables (rows wherever that id exists), as left and right (Column name and Value).

See below - (all 23 tables have the Quote ID but have different columns (some are same) so I would like it to display like the Details page that Webassist creates, plus one displaying underneath the other.(or similar to Email that is sent where all the $_POSTS are keys value pairs), so the Columns would be the $PKey since all the tables have differing columns, and the value would display on the right of it. I have some understanding of Mysql JOINS so I found one online but not doing exactly what I want.

This is too complex for me to describe here without direct assistance. We would have to work on this in a premier support ticket. Give me a call tomorrow and we can schedule an hour to figure it all out.

You can add this to the next premium support ticket:

Why doesn't this work? I was trying to dynamically read the table column names and data. It tells me the below error but I guess I don't really know how to write the syntax: (this will help me with what my previous question in the premium support ticket).

Warning: mysqli_query() expects parameter 2 to be string, object given in /home/jvdydf5kad9w/public_html/admin/quoterequest/Untitled-4.php on line 45

<?php
$query = mysqli_query($convacc_cx,$WADAquote_request_all);
$columns = mysqli_field_count($convacc_cx);
//$columns = $query->field_count;
for($i = 1; $i < $columns; $i++) {

//read field name
//$fieldName = mysql_field_name($WADAquote_request_all,$i);
$fieldName = mysqli_fetch_fields($WADAquote_request_all, $i)->name;
while($row = mysqli_fetch_assoc($WADAquote_request_all,$i)){
foreach($row as $column=>$value) {
echo "$column = $value\n";
}
echo $fieldName . "=". $row[$fieldName];
}
}
?>

line 45 is: $query = mysqli_query($convacc_cx,$WADAquote_request_all);

Thanks.

Levin

$WADAquote_request_all is already a recordset result, so it can't be passed into the mysqli_query() function.

You can rewrite this:

<?php

$query = mysqli_query($convacc_cx,$WADAquote_request_all);    

$columns = mysqli_field_count($convacc_cx); 

//$columns = $query->field_count;     

for($i = 1; $i < $columns; $i++) { 



//read field name

  //$fieldName = mysql_field_name($WADAquote_request_all,$i);

  $fieldName = mysqli_fetch_fields($WADAquote_request_all, $i)->name;        

  while($row = mysqli_fetch_assoc($WADAquote_request_all,$i)){

    foreach($row as $column=>$value) {

      echo "$column = $value\n";

    }

    echo $fieldName . "=". $row[$fieldName];   

  }

}

?>

to this:

<?php

for ($x=0; $x<sizeof($WADAquote_request_all->Results); $x++) {

  foreach($WADAquote_request_all->Results[$x] as $column=>$value) {

      echo "$column = $value\n";

  }

}

?>

Amazing! that did it. Thanks!

This is the last thing and I believe I will have everything the way I want. Again, you can add this to premium ticket.

The below mysql statement only displays the columns and data that are different (it's joining them), But I want to select (display) ALL columns from both tables with the product data, not only the different ones. (Once I get this working I can do for all 23 tables).

(fyi - The main table with the persons name, address, etc. is called quote_request, then the other tables that hold the info on the product they are requesting a quote on all have a suffx (e.g. quote_request_cs and quote_request_ir))

$WADAquote_request_all->setQuery("SELECT qr.quote_id_person, cs.*, ir.*
FROM quote_request AS qr
LEFT JOIN quote_request_cs AS cs ON
qr.quote_id_person = cs.quote_id_person
LEFT JOIN quote_request_ir AS ir ON
qr.quote_id_person = ir.quote_id_person
WHERE qr.quote_id_person = '" . $quoteid . "'");

The tables have different amt of column number with some similar names and some different. They are in fact different tables.

Thank you.

You probably just have to use AS statements to give each column a unique name like:

SELECT qr.quote_id_person, cs.quote_id_person as cs_quote_id_person, ir.quote_id_person AS ir_quote_id_person, ... FROM ...

That way you will have the unique column references to use when displaying the values on the page.

Yeah, that seems to work on this test. But. wowe- that's a big task with 23 forms and lots of fields. I'll have to do it like that if that's the only way. Thanks. (If you come up with a shorter way, lmk).

I also just noticed that I can select one and all (or several and all (the rest)).. such as -
Select cs.Width_Min as cs_Width_Min, cs.*,
or
Select cs.quote_id_person as cs_quote_id_person, cs.Width_Min as cs_Width_Min, cs.*,

Update - it ignores my cs.*, ir.* etc once I started defining every column so I have to define every column. There's probably more advanced method (beyond me at this point) to have 'For each column, add the suffix of the table name which comes after the dash(underscore)'. I only did the '_cs' at this point and below is what I have (it's working).

SELECT qr.quote_id_person,
cs.quote_id as cs_quote_id, cs.quote_id_person as cs_quote_id_person, cs.create_date as cs_create_date, cs.Product_Name as cs_Product_Name, cs.Specs_Name as cs_Specs_Name, cs.Machine as cs_Machine, cs.Material as cs_Material, cs.Thickness_Min as cs_Thickness_Min, cs.Thickness_Max as cs_Thickness_Max, cs.Core_Size as cs_Core_Size, cs.Max_Roll_Diameter as cs_Max_Roll_Diameter, cs.Width_Min as cs_Width_Min, cs.Width_Max as cs_Width_Max, cs.Web_Tension_Lbs_PLI_Min as cs_Web_Tension_Lbs_PLI_Min, cs.Web_Tension_Lbs_PLI_Max as cs_Web_Tension_Lbs_PLI_Max, cs.Machine_Speed as cs_Machine_Speed, cs.Max_Roll_Weight as cs_Max_Roll_Weight, cs.Application as cs_Application, cs.Core_Material as cs_Core_Material, cs.Thru_Shaft_Diameter as cs_Thru_Shaft_Diameter, cs.Notes as cs_Notes, cs.Upload_File_Name as cs_Upload_File_Name, cs.DateTime as cs_DateTime,
ir.quote_id_person AS ir_quote_id_person,
abr.*, ap.*, br.*, cma.*, cs.*, dra.*, ehr.*, fbr.*, fmr.*, frtrim.*, ir.*, isa.*, nra.*, rfq.*, rmb.*, rs.*, sct.*, sm.*, sr.*, ssc.*, ssh.*, str.*, sttrim.*, tc.*, us.*, ws.*
FROM quote_request AS qr
LEFT JOIN quote_request_abr AS abr ON
qr.quote_id_person = abr.quote_id_person
LEFT JOIN quote_request_ap AS ap ON
qr.quote_id_person = ap.quote_id_person
LEFT JOIN quote_request_br AS br ON
qr.quote_id_person = br.quote_id_person
LEFT JOIN quote_request_cma AS cma ON
qr.quote_id_person = cma.quote_id_person
LEFT JOIN quote_request_cs AS cs ON
qr.quote_id_person = cs.quote_id_person
LEFT JOIN quote_request_dra AS dra ON
qr.quote_id_person = dra.quote_id_person
LEFT JOIN quote_request_ehr AS ehr ON
qr.quote_id_person = ehr.quote_id_person
LEFT JOIN quote_request_fbr AS fbr ON
qr.quote_id_person = fbr.quote_id_person
LEFT JOIN quote_request_fmr AS fmr ON
qr.quote_id_person = fmr.quote_id_person
LEFT JOIN quote_request_frtrim AS frtrim ON
qr.quote_id_person = frtrim.quote_id_person
LEFT JOIN quote_request_ir AS ir ON
qr.quote_id_person = ir.quote_id_person
LEFT JOIN quote_request_isa AS isa ON
qr.quote_id_person = isa.quote_id_person
LEFT JOIN quote_request_nra AS nra ON
qr.quote_id_person = nra.quote_id_person
LEFT JOIN quote_request_rfq AS rfq ON
qr.quote_id_person = rfq.quote_id_person
LEFT JOIN quote_request_rmb AS rmb ON
qr.quote_id_person = rmb.quote_id_person
LEFT JOIN quote_request_rs AS rs ON
qr.quote_id_person = rs.quote_id_person
LEFT JOIN quote_request_sct AS sct ON
qr.quote_id_person = sct.quote_id_person
LEFT JOIN quote_request_sm AS sm ON
qr.quote_id_person = sm.quote_id_person
LEFT JOIN quote_request_sr AS sr ON
qr.quote_id_person = sr.quote_id_person
LEFT JOIN quote_request_ssc AS ssc ON
qr.quote_id_person = ssc.quote_id_person
LEFT JOIN quote_request_ssh AS ssh ON
qr.quote_id_person = ssh.quote_id_person
LEFT JOIN quote_request_str AS str ON
qr.quote_id_person = str.quote_id_person
LEFT JOIN quote_request_sttrim AS sttrim ON
qr.quote_id_person = sttrim.quote_id_person
LEFT JOIN quote_request_tc AS tc ON
qr.quote_id_person = tc.quote_id_person
LEFT JOIN quote_request_us AS us ON
qr.quote_id_person = us.quote_id_person
LEFT JOIN quote_request_ws AS ws ON
qr.quote_id_person = ws.quote_id_person
WHERE qr.quote_id_person = '" . $quoteid . "'");

This post has been deleted.