close ad
WARNING: Do Not Install the DREAMWEAVER CC 2017 or 2018 Update »
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Get info from 2 tables?

Thread began 1/07/2011 10:57 am by willo009412397 | Last modified 2/09/2011 6:39 pm by willo009412397 | 3486 views | 15 replies |

willo009412397

Get info from 2 tables?

Hi
I have 2 problems i need help with using DataAssist. I'm trying too set-up a pet classifieds website, and need help extracting info from multiple tables. I have 2 tables within the database (customer) and (pets) and need too be able too get info from both. Do i run DataAssist 2 times?
My second problem is the customer backend i created using SecurityAssist for Registration, and DataAssist for the Delete page, Insert Page, Results Page. On the Results Page i wish it too show only the results for that customer, but its showing all results within the database. Thankyou in advance for help.

Karl

Sign in to reply to this post

twitchr377598

Hi willo,

A single query to get data from two tables would look something like this:

php:
"SELECT * FROM customer INNER JOIN pet ON (customer.customerID = pets.customerID)"



In the example above I am assuming your customer table is called "customer" and your pet table is called "pet". I am also assuming that the column that links the two tables together is the customerID. For instance, each pet entry has a customerID associated with it to link it to a customer.

For your second issue, your record set for the various customer back end pages needs to filter by the customerID.

For instance it may look something like:

php:
"SELECT * FROM customer WHERE customerID = '1'"



Of course there are many ways to get the customerID dynamically via either a POST from a form or GET from a form or a session variable etc. I believe by default dataassist uses GET. So in Dreamweaver for your recordset you would use a setting of: "Filter by customerID using URL = customerID"

-Twitch

Sign in to reply to this post

willo009412397

Hi Twitch
And thanks for your help on this, i understand the 2nd part on how too use customerID thanks for that. But the first about combining 2 tables, where would i place this code? is there a certain place it needs too go? thanks for you speedy reply

Sign in to reply to this post

twitchr377598

On pages that get information from the database you will have a recordset. It probably looks something like this: (keep in mind where I have "recordset" yours may say something else. Same with "yourdatabase".

php:
mysql_select_db($database_yourdatabase, $yourdatabase);

$query_recordset = "SELECT * FROM customer";
$recordset = mysql_query($query_recordset, $yourdatabase) or die(mysql_error());
$row_recordset = mysql_fetch_assoc($recordset);
$totalRows_recordset = mysql_num_rows($recordset);



The query above will get all the columns from the customer table. To display the information you would have a repeating region that shows all the customers. It may look something like:

php:
<table>

    <?php do { ?>
                <tr><td>
<?php echo $row_recordset['customerName']; ?>
</td></tr>
                              <?php } while ($row_recordset mysql_fetch_assoc($recordset)); ?>
</table>


If you wanted to pull data from both the customer and pet tables using one recordset instead of two, you would replace:

php:
"SELECT * FROM customer";



With the one I posted earlier:

php:
"SELECT * FROM customer INNER JOIN pet ON (customer.customerID = pet.customerID)"



Then you could have a repeat region like:

php:
<table>

    <?php do { ?>
                <tr><td>
<?php echo "Customer name: ".$row_recordset['customerName']."<br/>Pet name: ".$row_recordset['petName']; ?>
</td></tr>
                              <?php } while ($row_recordset mysql_fetch_assoc($recordset)); ?>
</table>
Sign in to reply to this post

willo009412397

Thankyou Twitch this is my 1st crack at PHP, but you have explained very clear there. Im going too try it all out now, fingers crossed

Sign in to reply to this post

twitchr377598

No problem willo. I remember when I first started with php (wasn't so long ago, I'm by no means a guru) and needed help. I was very grateful when people took the time to help me and explain stuff instead of posting a link and saying "here read this"...haha

I was on here posting a problem I had and saw your post. It's nice to be able to give some help back.

Hope it works out for you. Just remember that the names for columns, recordsets etc that I used may not be what you used so just pay attention to those things and you should be ok.

-Twitch

Sign in to reply to this post

willo009412397

Hi Twitch
Is it ok too ask for a little more help, i have added the code as you shown me. Its working great, im now able too get info from 2 tables many thanks. Now i have a problem the search page (Index) as there is Type, Breed, Price from table (petdata) and Country, City from table (customerData). When i search any country or city it still just brings all records from the database. Also from the Results page i added a querystring too carry the info over to the Details page But its not workng? Thanks for any help Twitch, i have added the Index, Results and Details pages for you too look at if you can.

Attached Files
ZipFiles.zip
Sign in to reply to this post

Jason ByrnesWebAssist

On the results page, you have added a second Data Assist search behavior for the country and city search rather then editing the existing one.
the issue with the second data assist search behavior is that it is set to use the "RSresultsCust" recordset instead of the "RSresults" recordset.



Don't add the second search behavior, just edit the existing one to add the new search criteria.


On the detail page, you are using 2 recordsets to return the data, this should be the same idea as on the results page, use one recordset with a join clause to get the data from both tables.

see the W3Schools sql join page for details on creating join queries:
sql_join.asp

Sign in to reply to this post

willo009412397

Hi Jason
Thanks for your help, you guys there at webassist always answer my post pretty quick. Ive read through the tut you put me onto, and attempted to do as it said. But now im getting an a

Parse error: syntax error, unexpected '}' in C:\xampp\htdocs\Petseen\Index.php on line 33

msg when i try too run the index page? heres the code

}if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

And im pretty much lost with it all, as its all new too me. If i knew that there was going too be this much messing and adjusting of code, i would never have even attempted this project. However im too far in now too give up. Ive added the pages so as you can run your expert eye over them.

Sorry too keep mithering but hey , its all i can do.

Attached Files
ZipFolder1.zip
Sign in to reply to this post

Jason ByrnesWebAssist

line 3:
}if (!function_exists("GetSQLValueString")) {

should be:
if (!function_exists("GetSQLValueString")) {

Sign in to reply to this post
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...