[RESOLVED] DB Select based on Multiple Parameters

wilburburns

OK, I have been searching both here and on the Web for a couple of days now and can't seem to find what I am looking for.

What I am trying to do is extract a specific record from the database based on the value of 2 records. I guess this could be called a Search of sorts, only the search is strictly defined based on the URL in the link to this page.

This is the URL I am trying to pass and get to extract the data. This creates the
data3-det.php?recordID=6&billinglengthID=2
Which are the correct values based on the master Database extraction.

<a href="data3-det.php?recordID=<?php echo $row_plan_all['product_id']; ?>&amp;billinglengthID=<?php echo $row_plan_all['billing_id']; ?>"> <?php echo $row_plan_all['plan_name']; ?>&nbsp; </a>

Here is the Code for data3-det.php

$colname_DetailRS1 = "-1";
if (isset($_GET['recordID'])) {
  $colname_DetailRS1 = $_GET['recordID'];
}
$billname_DetailRS1 = "-1";
if (isset($_GET['billinglengthID'])) {
  $billname_DetailRS1 = $_GET['billinglengthID'];
}
mysql_select_db($database_Test_Alp, $Test_Alp);
$query_DetailRS1 = sprintf("SELECT * FROM billings_products, products WHERE product_id = %s, billing_id = %s", GetSQLValueString($colname_DetailRS1, "int"),GetSQLValueString($billname_DetailRS1, "int"));
$DetailRS1 = mysql_query($query_DetailRS1, $Test_Alp) or die(mysql_error());
$row_DetailRS1 = mysql_fetch_assoc($DetailRS1);
$totalRows_DetailRS1 = mysql_num_rows($DetailRS1);

The Problem is the data3-det.php does not work and returns the following error.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' billing_id =' at line 1

Everything works correctly if I only select from a single parameter such as just 'recordID'. However, I can't get this to work when selecting from multiple records or if trying to use the second parameter only 'billinglengthid'. Therefore, I believe something is wrong with the second parameter, but I don't know what..

Thanks,
Cliff

laserlight

Perhaps the query you want is:

SELECT * FROM billings_products, products WHERE product_id = %s AND billing_id = %s

Oh, and try to avoid SELECT *. It is usually better to select only those columns that you will use.

wilburburns

Thank You... While your suggestion was not the complete solution, it definetely pointed me in the right direction. The Problem was in my Select Statement.

Here is the working Select Statement


$query_DetailRS1 = sprintf("SELECT * FROM billings_products, products WHERE products.plan_index = billings_products.product_id AND product_id = %s AND billing_id = %s" , GetSQLValueString($colname_DetailRS1, "int"),GetSQLValueString($billname_DetailRS1, "int"));

Problem 1: As Laserlight posted. I needed the AND statement between the two variables

Problem 2: I neglected to join the two database tables as I did in my Master page. billing_id and product_id are in different tables. WHERE products.plan_index = billings_products.product_id AND. Without Joining tables the data was not available to select from.

And I do plan to be much more specific with my Select Statement. Now that I have the basic info working, I can limit what is selected.

Thanks
Cliff

wilburburns

On a slightly different note, is there a way to link tables from two separate Databases for Displaying on a Web Page.

Or to link tables from a Database to any other data source (XML, Text File, etc).

I have some additional data that I want to display which is not part of the Database. I don't want to add this info to the original Database, because I didn't write it and it is part of a billing software that I don't want to break. The Structure could change somewhat with an update.

Cliff

laserlight

On a slightly different note, is there a way to link tables from two separate Databases for Displaying on a Web Page.

Or to link tables from a Database to any other data source (XML, Text File, etc).

You definitely can "link" the data by manipulating it in PHP. "Linking" them in other ways depends on the technology, e.g., SQLite has an ATTACH DATABASE feature, some kind of importing functionality from XML documents may be supported etc.