Inner Join not getting results 1

[Marine1969]

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?

$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

 

[ChrisHirst]

Not a php question, you need to ask in forum436 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.

 

[Marine1969]

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.

 

[vacunita]

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

 

[spamjim]

If you're preparing a statement ($str=$conn->prepare("...), why are you not using the ? placeholder?

https://www.w3schools.com/php/php_mysql_prepared_statements.asp

https://secure.php.net/manual/en/mysqli.prepare.php

 

[ChrisHirst]

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.

 

[Marine1969]

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

$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();

 

[spamjim]

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

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.

 

[Marine1969]

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.