SQL Query help

Hi mhollibush,
try adding curly brackets and see if that works.

Kindly, Ozan

Kindly, Ozan

that didn't change anything
I am confused on this... maybe I am going about it the wrong way in the query

Im assuming you are trying to print these results somewhere on a view/edit or add page.

I changed the code slightly and tried it locally, works for me, It shows me all 3 products from the product table.

I dont fully understand what you are trying to accomplish, i.e. you can show details of a master-detail relationship if you follow this tutorial:
In any case, I´m assuming that you have a reason to do it the way you are doing it. The code below works for me.

on the view page of the part, I have a tab that this query is on,

each part can be used in multiple products,

I want to list the products that this part will work on in the tab.
Using the code you posted, I get a blank tab?
your sample makes sense, but for some reason its not working

I see the problem now <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=82364&image=1&table=forumreplies' class='bbc_emoticon' alt=':rolleyes:' />
Use this code:

Then, in the visual editor of the view tab, add

where you want the parts list to appear.
Kindly, Ozan

Then, in the visual editor of the view tab, add

where you want the parts list to appear.
Kindly, Ozan

where do I put the query? Before display?

I am still getting a blank tab?

this is driving me crazy - I didn't think this would be a difficult thing to accomplish... I was wrong <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=82365&image=2&table=forumreplies' class='bbc_emoticon' alt=':blink:' />

Yep, I have it in View page => Before display.
Make sure to make a "full build" and hit ctrl + f5 in the browser to make sure it`s refreshed.
Did you read the link I provided to the tutorial? There might be an easier solution to accomplish what you are trying.
The only other thing I can think of is, Private message me with a TeamViewer ID and I can try to help you on your machine to see what the problem is.

I have to step away for work, but would really like to get this working.. I will message you later ( if you are available )
Thanks for your help

Just following up, did you manage to solve your problem?

No, ended up working late, Will be trying today
here is another question along the same lines.
from the cases table, I need to search the cases column for all the cases that have the same first 10 characters
I thought I could use

$sql = "SELECT * FROM cases WHERE LEFT(case,10) =".$data["case"]." ";
I guess I am more novice than I thought <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=82375&image=1&table=forumreplies' class='bbc_emoticon' alt=';)' />

Hi buddy,

is a special mysql comand. Just put it in `` like this:

this also works:

is a special mysql comand. Just put it in `` like this:

this also works:

DUH..... I feel stupid.... Never thought of that - Thanks....

Will be trying to fix the other issue later today.

I appreciate all your help

Well now I feel really stupid.

I am doing the query and its returning nothing

I know for a fact that there are multiple cases associated with a specific case I am trying to query

even running the query directly in the database through phpMyAdmin - I am getting nothing?

here is what I am needing to accomplish

the case numbers have a specific format xxxxxxxxxx-xxx

I need to be able to search the first 10 characters of the current case and pull all the matching cases
if there is a second, third, or fourth case associated, the only numbers that change would be the last three characters in the case xxxxxxxxxx-xxx
I thought I could do a LEFT('case',10) to search for all the cases with the first 10 characters based on the current records first 10 characters
can you think of an easier way?

Using LEFT(case,10) or "like" query like @dermemo suggested is the right way to do this.

I have tried this and the query never returns any results, even if I place a case that I know has other cases associated with the first 10 charactersin the query instead of the $data["case"]
this is my code:
echo "Associated Cases";

global $pageObject;

$data = $pageObject->getCurrentRecord();

$sql = "SELECT * FROM cases WHERE LEFT('case',10) = ".$data["case"]." ";

$rscases = CustomQuery($sql);
echo "<table width='80%' border='1' bordercolor='#C0C0C0' cellpadding='5' >";

echo "<tr>";

echo "<td>&nbsp;&nbsp;Case</td>";

echo "<td>&nbsp;&nbsp;Date</td>";

echo "<td>&nbsp;&nbsp;Start Time</td>";

echo "<td>&nbsp;&nbsp;End Time</td>";

echo "<td>&nbsp;&nbsp;Repair Type</td>";

echo "</tr>";

while($data1=db_fetch_array($rscases))
{

echo "<tr><td>&nbsp;&nbsp;<a href=cases_view.php?editid1=".$data1["caseId"].

">". $data1["case"]."</a></td>";

echo "<td>&nbsp;&nbsp;";

$data1["date"] = date("l M-d-Y ", strtotime($data1["date"]));

echo "". $data1["date"]."</td>";

echo "<td>&nbsp;&nbsp;". $data1["time"]."</td>";

echo "<td>&nbsp;&nbsp;". $data1["end_time"]."</td>";

echo "<td>&nbsp;&nbsp;". $data1["repairtype"]."</td></tr>";
}

echo "</table>";
any ideas?

I was able to get this first query resolved... Bone head move... forgot the ' in front and end of $partnumber at the end of the query..
so the new query looks like this in the tab view:
global $pageObject;

$data = $pageObject->getCurrentRecord();

$partnumber = $data["partnumber"];
$sql = "SELECT

parts.partId,

parts.partnumber,

parts.partDescrpt,

parts.product,

products.productId,

products.pnumber,

products.pdescrip

FROM

parts

INNER JOIN products ON parts.product = products.productId where partnumber = '$partnumber'";

$rsproducts = CustomQuery($sql);
echo "<table width='80%' border='1' bordercolor='#C0C0C0' cellpadding='5' >";

echo "<tr>";

echo "<td>&nbsp;&nbsp;Product Number</td>";

echo "<td>&nbsp;&nbsp;Description</td>";

echo "</tr>";

while($data1=db_fetch_array($rsproducts))
{

echo "<tr><td>&nbsp;&nbsp;<a href=products_view.php?editid1=".$data1["productId"].

">". $data1["pnumber"]."</a></td>";

echo "<td>&nbsp;&nbsp;". $data1["pdescrip"]."</td>";
}

echo "</table>";
[/size]

I am thinking its the format of the case number xxxxxxxxxx-xxx

if I do a query of the first 10 characters, it brings up all the cases that have those 10 characters.

As soon as I add the "-xxx" ( xxxxxxxxxx-xxx ) it doesn't return any results
any ideas on a way to get this to work?

When I run this query in the SQL in PHPR it returns the associated cases
SELECT

*

FROM cases

WHERE (LEFT(case,11) = '5320003016-')
when I run it in the application with the same case number it returns the results

5320003016-531

5320003016-532
when I use the full case number "5320003016-531" it doesn't fail, but doesn't return any results.
it should return the same results, but it doesn't

Any Ideas????

Hi buddy,
I think this is the Problem:

First, I suggest putting [size="2"]$data["case"] in a variable like:[/size]

[size="2"]Then since your field type is varchar, you need to put it in ' '[/size]
[size="2"]So try this:[/size]

Hope that helps you
Kindly, Ozan

First, I suggest putting [size="2"]$data["case"] in a variable like:[/size]

[size="2"]Then since your field type is varchar, you need to put it in ' '[/size]
[size="2"]So try this:[/size]

Hope that helps you
Kindly, Ozan

tried this as you described, still nothing...

I am stumped...
like I had stated...

if I do a query with the first 10 characters of the case, it will bring up all the cases with those characters, but as soon as I query the full 14 characters, it will not display any results.

It is still a little confusing but I believe I'm starting to understand. You need to truncate the the search value before search is applied.
You cannot do that in SQL Query as the value that user types into search box is not known in advance. What you need to do is to use Search API to replace

with

Do not make any changes to original SQL Query. Check this article and use similar approach:

http://asprunner.com/forums/topic/24763-search-master-and-details-tables-together/
Also make sure you know how to troubleshoot your queries:

https://xlinesoft.com/phprunner/docs/debugging_tips.htm

with

Do not make any changes to original SQL Query. Check this article and use similar approach:

http://asprunner.com/forums/topic/24763-search-master-and-details-tables-together/
Also make sure you know how to troubleshoot your queries:

https://xlinesoft.com/phprunner/docs/debugging_tips.htm

here is what I am trying to accomplish.

each case can have additional cases associated with it

example:

first case: 1234567890-111

second case: 1234567890-112

third case: 1234567890-113
on the view page of each case, I want to be able to show the associated cases, only the first 11 characters will match 1234567890-

the last three characters will change 111, 112, 113
the issue I am running into -

I need to run a search of the column "case" and return all the records that match the first 11 characters xxxxxxxxxx- based on the current record set ".$data["case"]."

the issue is that the case number is 14 characters long - how would I get it to search only the first 11 characters
I hope that makes sense -
I looked at the pages you sent, and the API makes sense, but I need to somehow only look at the first 11 characters.. this is where I am lost

I am still digging and testing <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=82402&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />

@mhollibush

Did you try dermemo's suggestion ?

$case =$data["case"];

$sql = "SELECT FROM cases WHERE LEFT('case',10) = '$case' ";
or slightly the same
$case =$data["case"];

$sql = "SELECT FROM cases WHERE LEFT('case',10) = '".$case."' ";
Does this help?

This should return you all rows where 'case' matches the selection . As a result 'case' will then show its 14 characters value.
Is your problem making the selection right in the sql-clause?

Is your problem just with returning the last 3 characters of the 'case' field ?

Is your problem both of the above?
**
@mhollibush

I did also change your code to this:
echo "Associated Cases";

global $pageObject;

$data = $pageObject->getCurrentRecord();

$sql = "SELECT FROM cases WHERE LEFT('case',10) = '".$data["case"]."' "; // mind the single and the double quotes and the dots around $data["case"]!

// or alternatively replace the line above with these 2 lines

// $case=$data["case"];

// $sql = "SELECT FROM cases WHERE LEFT('case',10) = '".$case."' ";
$rscases = CustomQuery($sql);
echo "<table width='80%' border='1' bordercolor='#C0C0C0' cellpadding='5' >";

echo "<tr>";

echo "<td>&nbsp;&nbsp;Case</td>";

echo "<td>&nbsp;&nbsp;Date</td>";

echo "<td>&nbsp;&nbsp;Start Time</td>";

echo "<td>&nbsp;&nbsp;End Time</td>";

echo "<td>&nbsp;&nbsp;Repair Type</td>";

echo "</tr>";

while($data1=db_fetch_array($rscases))
{

// echo "<tr><td>&nbsp;&nbsp;<a href=cases_view.php?editid1=".$data1["caseId"].">". $data1["case"]."</a></td>";

// mind that $data1["case"] in the above line will return the 14 characters value

// if you want to return only the three last characters then you should use right($data1["case"],3) in stead of $data1["case"]
echo "<tr><td>&nbsp;&nbsp;<a href=cases_view.php?editid1=".$data1["caseId"].">". right($data1["case"],3)."</a></td>";
echo "<td>&nbsp;&nbsp;";

$data1["date"] = date("l M-d-Y ", strtotime($data1["date"]));

echo "". $data1["date"]."</td>";

echo "<td>&nbsp;&nbsp;". $data1["time"]."</td>";

echo "<td>&nbsp;&nbsp;". $data1["end_time"]."</td>";

echo "<td>&nbsp;&nbsp;". $data1["repairtype"]."</td></tr>";
}

echo "</table>";
Does this return what you want?