I don't understand how the PHP code block works

[Ron Austin]

I created a PHP code block and wrote a script that opens the database, reads some records and prints the results.  It works fine as a stand alone program but when I put it in the PHP code block it causes the front page to repeat and the php code block does not display correctly.  I tried this with Simple Portal and Tiny Portal and got the same bad results.  What am I missing here?

[Aleksi "Lex" Kilpinen]

What exactly is it that you are trying to use in the block?

[Ron Austin]

When this didn't work I supplied the full path to the include file and that didn't help so removed that line and defined the $username, $password and $database variables.  That did allow me to connect to the database and read records but the front page was still dupicated and the php block did not display properly.  It also did not like the single quotes so I switched from single quote, double quote to double quote, single quote and that did let me read the database.

This is for a club website where the block displays the Meeting date, time and place, the meal before the meeting date, time and place, the board meeting date time and place and the date time and place of the amateur radio date time and place.  If it worked then someone could change all that from a setup screen without using the block editor.

Code Select Expand

include "aadb.php";
$find="MEETING";
$con=mysqli_connect('localhost',$username,$password,$database);
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
$search="SELECT * FROM eats  WHERE cat ='$find'";
if(!$result=mysqli_query($con,$search)) {
die('There was an error running the query [' . $con->error . ']');
}
$num=mysqli_num_rows($result);
if ($num=="0") die ("record $find not found!");?>
<?php 
$row = mysqli_fetch_assoc($result);
$name=$row['name'];
$street=$row['street'];
$city=$row['city'];
$zip=$row['zip'];
$link=$row['link'];
$rest=$row['rest'];
$comment=$row['comment'];
$datetime=$row['datetime'];
$restdate=$row['restdate'];
echo"<b>NEXT REGULAR MEETING:</b><br>";
echo"$datetime<br>";
echo"$name<br>";
echo"$street<br>";
echo $city." ".$zip."<br>";
if ($comment<>"") echo"$comment<br>";
echo "<a href='".$link."'>Click for map</a><br><br>";

$find="BOARD";
$search="SELECT * FROM eats  WHERE cat ='$find'";
if(!$result=mysqli_query($con,$search)) {
die('There was an error running the query [' . $con->error . ']');
}
$num=mysqli_num_rows($result);
if ($num=="0") die ("record $find not found!");?>
<?php 
$row = mysqli_fetch_assoc($result);
$name=$row['name'];
$street=$row['street'];
$city=$row['city'];
$zip=$row['zip'];
$link=$row['link'];
//$rest=$row['rest'];
$comment=$row['comment'];
$datetime=$row['datetime'];
echo "<b>NEXT BOARD MEETING:</b><br>";
echo"$datetime<br>";
echo"$name<br>";
echo"$street<br>";
echo $city." ".$zip."<br>";
if ($comment<>"") echo"$comment<br>";
echo "<a href='".$link."'>Click for map</a><br><br>";

$find=$rest;
$search="SELECT * FROM eats  WHERE id ='$find'";
if(!$result=mysqli_query($con,$search)) {
die('There was an error running the query [' . $con->error . ']');
}
$num=mysqli_num_rows($result);
if ($num=="0") die ("record $find not found!");?>
<?php 
$row = mysqli_fetch_assoc($result);
$name=$row['name'];
$street=$row['street'];
$city=$row['city'];
$zip=$row['zip'];
$link=$row['link'];
$rest=$row['rest'];
$comment=$row['comment'];
$datetime=$row['datetime'];
echo "<b>MEET & EAT:</b><br>";
echo"$restdate<br>";
echo"$name<br>";
echo"$street<br>";
echo $city." ".$zip."<br>";
echo"$comment<br>";
echo "<a href='".$link."'>Click for map</a><br><br>";

$find="TESTING";
$search="SELECT * FROM eats  WHERE cat ='$find'";
if(!$result=mysqli_query($con,$search)) {
die('There was an error running the query [' . $con->error . ']');
}
$num=mysqli_num_rows($result);
if ($num=="0") die ("record $find not found!");?>
<?php 
$row = mysqli_fetch_assoc($result);
$name=$row['name'];
$street=$row['street'];
$city=$row['city'];
$zip=$row['zip'];
$link=$row['link'];
//$rest=$row['rest'];
$comment=$row['comment'];
$datetime=$row['datetime'];
echo "<b>NEXT LICENSE TESTNG:</b><br>";
echo"$datetime<br>";
echo"$name<br>";
echo"$street<br>";
echo $city." ".$zip."<br>";
if ($comment<>"") echo"$comment<br>";
echo "<a href='".$link."'>Click for map</a><br><br>";
?>
<html>
<a href="https://www.arrl.org/what-to-bring-to-an-exam-session"  target=_blank><span style="color:red"><b>Click to show what to bring to exam</b></span></a>
<br>
<br>
Testing is also available on request.
</html>

[Arantor]

First things first, you can't really do the awful jumping in-and-out of PHP thing in a PHP block. It's not really supported, it's considered a legacy behaviour even by the PHP team at this point and there are debates on how to remove it entirely from the language.

If you remove that and just have it all be echo statements, where does that get you to?

Also, you shouldn't really have an html element in the output, there's already one on the page in SMF.

[@rjen]

A php block executes code that it considers to be php. You should not have any <?php tags in the code: these will be added by the portal...

[Ron Austin]

I removed the <?php tags and the html code at the bottom.  I also changed all the single quotes to double quotes and visa versa.  Some things are still not working right but I will keep working on it.  Now I am seeing:


NEXT REGULAR MEETING:
March 21 -- 07:00 PM
Lake City Baptist Temple
3061 FL-47
Lake City FL 32025
Click for map

In the block.  There is a problem with the Click for map link, it is adding my site url to the beginning of the link, https://kk4hgy.com/https://maps.google.com/------.  How do I get rid of that?  5:11 am here and way past my bedtime.  Thanks for all your help.

[Steve]

As this really isn't a problem with SMF as it is 'out of the box', moving this to the 'SMF Coding Discussion' board.

[Ron Austin]

okay, here is another problem:

$find="MEETING";
$search="SELECT * FROM eats  WHERE cat ='$find'";
if(!$result=mysqli_query($con,$search)) {
 die('There was an error running the query [' . $con->error . ']');
 }

and I get this error message:
There was an error running the query [You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''MEETING'' at line 1]

If I use
$SEARCH = "SELECT from eats";  then I get no error.

I noticed that the error message mentions, "your MariaDB server".  what is that?

[Aleksi "Lex" Kilpinen]

MariaDB is a fork of MySQL, pretty common with commercial hosts these days.

[Arantor]

Well, last point first, MariaDB is a variant of MySQL that appeared after MySQL was bought out.

The error that you have is likely the result of PHP doing odd things with quotes; both double quotes and single quotes have meaning in PHP and they are not properly interchangeable. Meanwhile, both double quotes and single quotes *also* have meaning in MySQL/MariaDB and again are also not properly interchangeable.

SMF itself doesn't ever write queries that way because if that content could ever come from the user it would be dangerously insecure and SMF is explicitly hardened against it.

*thinks for a moment*

Try it this way around.

Code Select Expand

$find = 'MEETING';
$search = 'SELECT * FROM eats WHERE cat = \'' . $find . '\'';
if (!($result = mysqli_query($con, $search)) {
    die('There was an error running the query [' . $con->error . ']');
}

Though die() in a PHP block is also a bad idea.

[Ron Austin]

no, that didn't work, but this does: $search="SELECT * FROM eats WHERE cat = \"$find\" ";

so it seems that instead of using single quotes inside double quotes I can use \" and it works (so far)

[Arantor]

Weird, single quote is the correct quote type for string literals in MariaDB and ANSI SQL in general...