Can I use select COUNT(*) in pdo

multichild · October 7, 2016, 7:57am

Ah cheers colshrapnel, I just learned more about pdo from that one post that I have all week!

Will implement this and move onto the next, but the good thing is I can use your post to go back through my code and change it to proper pdo, not my half hearted attemps.

multichild · October 7, 2016, 8:20am

Maybe Dreamweaver isnt up to date, but the line below works, but I get a red warning in Dreamweaver, but like I said when I tested it it was fine, I will leave it but thought id post just in case

$stmt->execute(["%$_POST[id2]%", $_POST['id']]);

droopsnoot · October 7, 2016, 9:02am

Maybe the issue is with the version of Dreamweaver - is the square-bracket syntax to define an array something fairly new to PHP that DM might not yet understand, or am I confusing it with something else?

<span data-id="<?php echo $tutorial_id; ?>"

This seems a much “better” way of passing data around than the method from the tutorial of using the span id - just seems more appropriate somehow.

multichild · October 7, 2016, 9:05am

Ye it must be with Dreamweaver, its working fine, no errors so will leave it be.

And yes that data suggestion is much easier to deal with, and like you say make sense as you can use the data better.

multichild · October 7, 2016, 2:02pm

Hi colshrapnel,

I followed the steps in your thread to go through a few other pdo statements and in the one below which is slightly different from the one I posted earlier and iM getting a warning.

$query="select Nom_Hot, IdType_Hot, Id_Hot, IdRsrt_Hot, Dir_Hot, IdCat_Hot, Act_Hot, Foto1_Hot from tbl_hotels LEFT JOIN tbl_resorts ON (tbl_resorts.Id_Rsrt=tbl_hotels.IdRsrt_Hot) WHERE tbl_resorts.Nom_Rsrt like ? AND Act_Hot=1 AND      tbl_hotels.Id_Hot < ? ORDER BY tbl_hotels.Id_Hot DESC LIMIT $showLimit";
$resultb = $dbo->prepare($query); 
$resultb->execute(["%$_POST[id2]%", $_POST['id']]); 
$num_rowsb = $resultb->rowCount();

if($num_rowsb > 0){
foreach ($dbo->query($resultb) as $nt) {

And on that last line Im getting this error

Warning: PDO::query() expects parameter 1 to be string, object given in

Warning: Invalid argument supplied for foreach() in

I have a very annoying loop issue and basically am trying to make sure my code is squeeky clean to try and work it out.

Basically the very last show more works correctly, but there a second loop that appends it again to the div, and I cant work it out.

colshrapnel · October 7, 2016, 2:04pm

foreach ($resultb as $nt) {

multichild · October 7, 2016, 2:06pm

Cheers, that got rid of the error and it worked perfectly, but loop issue still there.

colshrapnel · October 7, 2016, 2:10pm

Sorry, I have no idea what is this loop issue. May be it’s malformed HTML or something.

multichild · October 7, 2016, 2:12pm

Ye I know you cant see it, and the problem I got is Im working behind a VPN, so can only show the code.

I’ve been through it all, and the loop is strange as it always double posts the last lot of hotels.

Id post it all, but Im sure you got better things to be doing on a Friday afternoon

colshrapnel · October 7, 2016, 2:14pm

indeed

multichild · October 7, 2016, 2:21pm

lol, I tell you what I’ll post it anyway just in case you get bored

if(isset($_POST["id"]) && !empty($_POST["id"])){    
if(isset($_POST["id2"]) && !empty($_POST["id2"]))

$queryAll="select count(*) from tbl_hotels LEFT JOIN tbl_resorts ON (tbl_resorts.Id_Rsrt=tbl_hotels.IdRsrt_Hot) WHERE tbl_resorts.Nom_Rsrt like ? AND Act_Hot='1' AND (tbl_hotels.Id_Hot < ?) ORDER BY tbl_hotels.Id_Hot DESC"; 
$stmt=$dbo->prepare($queryAll);
$stmt->execute(["%$_POST[id2]%", $_POST['id']]);
$count = $stmt->fetchColumn();

$showLimit = 9;

$query="select Nom_Hot, IdType_Hot, Id_Hot, IdRsrt_Hot, Dir_Hot, IdCat_Hot, Act_Hot, Foto1_Hot from tbl_hotels LEFT JOIN tbl_resorts ON (tbl_resorts.Id_Rsrt=tbl_hotels.IdRsrt_Hot) WHERE tbl_resorts.Nom_Rsrt like ? AND Act_Hot=1 AND tbl_hotels.Id_Hot < ? ORDER BY tbl_hotels.Id_Hot DESC LIMIT $showLimit";
$resultb = $dbo->prepare($query); 
$resultb->execute(["%$_POST[id2]%", $_POST['id']]); 
$num_rowsb = $resultb->rowCount();

if($num_rowsb > 0){
foreach ($resultb as $nt) {
$tutorial_idb = $nt['Id_Hot']; ?>
<div class='obj'><a href='#'><p class='text'>REQUEST REPORT 2</p><div class='item'><img src=http://www.checksafetyfirst.com<?php echo $nt['Foto1_Hot']?> alt='pepsi' width='360' height='239'><div class='item-overlay top'></div></div></a><p class='nameHotel'><?php echo $nt['Nom_Hot'] ?></p><p class='addressHotel'><?php echo $nt['Dir_Hot'] ?></p></div>
<?php } ?>
<div class="obj push"></div>
<div class="obj push"></div>
<div class="pushend"></div>
<?php  if($count > $showLimit){ ?>
<div class="show_more_main" id="show_more_main<?php echo $tutorial_idb; ?>">
<span data-id="<?php echo $tutorial_idb; ?>" data-id2="<?php echo $_POST['id2']; ?>" class="show_more" title="Load more posts">SHOW MORE SECOND</span>
<span class="loding" style="display: none;"><span class="loding_txt">Loading….</span></span><br/><br/>
</div>
<?php } } }?>

This is what that code is communicating with, and I just simply cant work it out. It basically works but the very last load of hotels that are less than the showLimit, outputs for a second time and creates itself a whole new div to and papends itsel to the original div.

<script>
$(document).ready(function(){
$(document).on('click','.show_more',function(){
var id = this.dataset.id;
var id2 = this.dataset.id2;
$('.show_more').hide();
$('.loding').show();
$.post('ajax-search-demock_3.php', this.dataset, function (html) {
$('#show_more_main'+id).remove();
$('.tutorial_list').append(html);
});
});
});
</script>

Have a nice weekend everyone

Mittineague · October 7, 2016, 3:08pm

Is this line working for you? i.e. no query error messages.

$resultb->execute(["%$_POST[id2]%", $_POST['id']]);

I don’t recall the specific details, but a while back I had some problems around using wildcards in statements and needed to put the string together first. eg. something like this

$wildcarded_id2_str = "%" . $_POST[id2] . "%";
$resultb->execute([$wildcarded_id2_str, $_POST['id']]);

BTW, unless id2 is a CONSTANT it should be in quotes.

colshrapnel · October 7, 2016, 3:39pm

Regarding wildcards, both your statements are effectively equal, either “putting the string together”.

While your second example indeed will produce an error, because of unquoted string in the array’s key. So it’s better to keep it as is

Mittineague · October 7, 2016, 4:29pm

I dug up the older code and the reason I did as I posted was for a reason different than error problems.

I think the problem I was thinking of was that I had first tried putting the wildcards in the query itself around the placeholder.

multichild · October 7, 2016, 4:41pm

It seems to working yes as the initial loop works, but in Dreamweaver it is highlighting it as a problem line, but as it was working on the dev server I left it. But I did find something strange, as I was posting earlier I wanted to show you an example, so I pasted the files onto the live server uploaded them and it didnt work, white out.

So not sure if perhaps the servers are different for now, the live being older, but I didnt have time to test it and see, but it was strange that worked fine on dev server and not on live.

I’m out of the office now, so everything is off the top of my head

droopsnoot · October 7, 2016, 5:58pm

I’m not sure if this is your exact code, but I see a few problems in here:

if(isset($_POST["id2"]) && !empty($_POST["id2"]))

Because there’s no open-curly-brace here, only the next line is conditional on the second if() statement, and the code from the prepare() on will run regardless of whether id2 is set. So I’m surprised the logic doesn’t go a bit strange, unless it’s a copy/paste problem.

$stmt->execute(["%$_POST[id2]%", $_POST['id']]);
....
$resultb->execute(["%$_POST[id2]%", $_POST['id']]);

You do this twice in the code, the first entry isn’t correct because you haven’t put quotes around the array index id2. Like he said above:

Maybe they are both copy/paste issues.

multichild · October 7, 2016, 6:26pm

So with that one you put an open curly at the top is it, and then another at the bottom, or do yu just put an open and close curly at the end, damn shame I cant get at the code now until Monday
if(isset($_POST[“id2”]) && !empty($_POST[“id2”])) {}

If you had time could you grab my code above and put your changes in, so I can see, and hopefully get it working on Monday
Like that?

And with the second issue you picked up

$stmt->execute(["%$_POST[id2]%", $_POST['id']]);

Can these 2 below be incorporated into one line so the open curly bracket can stay as it is and close at the end

if(isset($_POST["id"]) && !empty($_POST["id"])){    
if(isset($_POST["id2"]) && !empty($_POST["id2"]))

Sorry again I can see quotes so you must mean something else, could you show me, sorry

multichild · October 8, 2016, 7:47am

Got my sentences mixed up with the line above, I said sorry I can see quotes around the array index id2, so you must have meant something else than I can see

colshrapnel · October 8, 2016, 8:39am

Indeed square braces could be a problem, as they are not supported by ancient and abandoned versions of PHP which your host may be running.

For that case change it to

$resultb->execute(array("%$_POST[id2]%", $_POST['id']));

but it will be much better if you peek into error log and get the actual error massage

And for a reason. He is running two queries and thus apparently needs 2 execute()

Both entries are the same and both are correct.

colshrapnel · October 8, 2016, 8:43am

Yes you always can

if(isset($_POST["id"]) && !empty($_POST["id"] && isset($_POST["id2"]) && !empty($_POST["id2"])) {

However here is the code you need

if(!empty($_POST["id"] && !empty($_POST["id2"])) {

as running both isset and empty makes no sense