×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Inner Join not getting results

Inner Join not getting results

Inner Join not getting results

(OP)
I am trying to use an inner join to display a report but am not getting any results. I have the sql in workbench and the results show up there so I'm not sure why I am not getting it through the php. Below is the code that I am echoing to the screen. $d1 and $d2 are beginning and ending dates. Below that is the results.... What am I doing wrong here?

CODE --> php

$str=$conn->prepare("Select invoices.idinvoice, invoices.invoice, invoices.idcst, invoices.ddate, invoices.chargec, invoices.chargeb, invoices.fuel, invoices.billingtotal, customers.name, customers.city "
. "From invoices Inner Join customers On invoices.idcst = customers.idcst "
. "Where ddate Between '" . $d1 . "' And '" . $d2 . "' And idclient = " . $row['idclient'] . " "
. "Order By invoices.ddate, customers.name"); 

Select invoices.idinvoice, invoices.invoice, invoices.idcst, invoices.ddate, invoices.chargec, invoices.chargeb, invoices.fuel, invoices.billingtotal, customers.name, customers.city From invoices Inner Join customers On invoices.idcst = customers.idcst Where ddate Between '2018-06-25' And '2018-07-01' And idclient = 2 Order By invoices.ddate, customers.name

RE: Inner Join not getting results

Not a php question, you need to ask in forum436: MySQL and include ONLY the concatenated query in a code box please.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.

Never mind this jesus character, stars had to die for me to live.

RE: Inner Join not getting results

(OP)
Actaully, I have the results in MYSQL but I am trying to code the PHP to display in my webpage. It is the PHP code that is not working. I have removed the echo from the code box.

RE: Inner Join not getting results

Quote (Marine1969)

I have removed the echo from the code box.

Why?

If its the PHP code that is not working, then you need to show that code.

Where are you executing the query? All you are showing is the prepare statement. You need to execute it after its prepared to get any results.

Is it returning any results after being executed?

If it is returning results, is the issue just with the output to screen?

We need to see the PHP code you are using to run the query.

And what the issue is exactly.










----------------------------------
Phil AKA Vacunita
----------------------------------
OS-ception: Running Linux on a Virtual Machine in Windows which itself is running in a Virtual Machine on Mac OSx.

Web & Tech

RE: Inner Join not getting results

Quote:

It is the PHP code that is not working.
Then it is the PHP code that is needed for this forum, if the concatenated query returns an appropriate result, the problem is elsewhere in the script.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.

Never mind this jesus character, stars had to die for me to live.

RE: Inner Join not getting results

(OP)
spamjim,

I am pretty much self taught and always looking to learn to be a better programmer. In this case I am not inserting into the db so I am not concerned about injection here. Should I be? I will check out the links this weekend.

Vacunita,

The code technically runs without returning any results. I have 3 test records that should be showing up. This is where the problem is, not returning the results. I modified the select statement to test if I had things in the correct order as I normally do not use joins; ie From, Where and Order By. I did get my results directly in mysql.

I hope this helps shine a little light on the issue. Thanks everyone!
Here is the entire code I have for trying to retrieve records...

CODE --> php

$str=$conn->prepare("Select invoices.idinvoice, invoices.invoice, invoices.idcst, invoices.ddate, invoices.chargec, invoices.chargeb, invoices.fuel, invoices.billingtotal, customers.name, customers.city "
	            . "From invoices Inner Join customers On invoices.idcst = customers.idcst "
	            . "Where ddate Between '" . $d1 . "' And '" . $d2 . "' And idclient = " . $row['idclient'] . " "
	            . "Order By invoices.ddate, customers.name");
$str->execute();
$inv=$str->fetchAll(); 

RE: Inner Join not getting results

One of the reasons to use prepared statements is to avoid injection. You may have intended a SELECT but an injection might turn that query into an INSERT, DROP or a completely different SELECT you never expected.

If you can add this line...

CODE

print ("Select invoices.idinvoice, invoices.invoice, invoices.idcst, invoices.ddate, invoices.chargec, invoices.chargeb, invoices.fuel, invoices.billingtotal, customers.name, customers.city "
	            . "From invoices Inner Join customers On invoices.idcst = customers.idcst "
	            . "Where ddate Between '" . $d1 . "' And '" . $d2 . "' And idclient = " . $row['idclient'] . " "
	            . "Order By invoices.ddate, customers.name"); 

...run the page in a browser, copy the printed query into Workbench, and test it there successfully, then you know the error is in the other PHP you're not showing here.

RE: Inner Join not getting results

(OP)
Found the issue. In the 'And' the idclient needed to have the table added. Thanks guys. Spamjim, I never thought to take that screen output and put into workbench. Doing that told me where the issue was.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close