close ad
Databridge V2 with MySQLi support IS Now Available!
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

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

Thread began 4/29/2019 11:25 am by lr_leal239405 | Last modified 5/14/2019 2:33 pm by Ray Borduin | 116 views | 12 replies |

lr_leal239405

Control panel that displays like Webassist Email block. - 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.

Sign in to reply to this post

Ray BorduinWebAssist

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.

Sign in to reply to this post

lr_leal239405

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

Sign in to reply to this post

Ray BorduinWebAssist

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

You can rewrite this:

php:
<?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:
<?php

for ($x=0$x<sizeof($WADAquote_request_all->Results); $x++) {
  foreach(
$WADAquote_request_all->Results[$x] as $column=>$value) {
      echo 
"$column = $value\n";
  }
}
?>
Sign in to reply to this post

lr_leal239405

Amazing! that did it. Thanks!

Sign in to reply to this post

lr_leal239405

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.

Sign in to reply to this post

Ray BorduinWebAssist

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.

Sign in to reply to this post

lr_leal239405

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.*,

Sign in to reply to this post

lr_leal239405

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 . "'");

Sign in to reply to this post

lr_leal239405

This post has been deleted.

loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...