Hello Errol,

I've edited your original post to obscure your database connection credentials...  it's best not to post these on a public forum for security reasons, for your own sake.

There were some errors in your PHP syntax.  The revised code is as below:

<?php

$servername = "******";
$username = "******";
$password = "******";
$dbname = "******";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

//$sql = "SELECT order_id,field_id, label, value FROM cartinvoice_addresses";
// Here is where I am having and issue$sql = "SELECT order_id,field_id, label, value,price,name FROM cartinvoice_addresses INNER JOIN ON cartinvoice_addresses.order_id = cartproducts.order_id";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {if ($row["field_id"] == "Address1"){echo "<br><br>";}

echo " " . $row["value"].$row["name"];}

}

//Added code

else {echo "0 results";}

?>

However, as I'm unsure what you are attempting to do with the database data, I can't comment (for the time being anyway) on your SELECT-JOIN clause.  I would expect a table name immediately after 'INNER JOIN' but preceding 'ON'

Kind regards,

Paul

Search the WebSite X5 Help Center

In regards to the credentials, they were incorrect. But Thanks. My plan is to get data from two tables, Then print one row with the combined data. When I work with one table all is well.

Hello Paul,

I added the table as you suggested, I got the same result http 500.

<?php
$servername = "localhost";
$username = "Delta";
$password = "Dst1913";
$dbname = "BuyTicket";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
//$sql = "SELECT order_id,field_id, label, value FROM cartinvoice_addresses";
//$sql = "SELECT order_id,field_id, label, value FROM cartinvoice_addresses";
// Here is where I am having and issue
$sql = "SELECT order_id field_id, label, value, price, name
FROM cartinvoice_addresses
INNER JOIN cartinvoice_addresses ON cartinvoice_addresses.order_id = cartproducts.order_id;
//
$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
if( $row["field_id"] == "Address1"){ echo "<br><br>"; }
echo " " . $row["value"].$row["label"].$row["price"].$row["name"];
//Added code

}
} else {
echo "0 results";
}
$conn->close();
?>

Can you be as specific as possible about exactly which data you want to extract from the database?  You'll need very specific syntax in your SELECT statement.  The SELECT statement in the code above is not properly formed.

For example, do you want to take the entire data from both tables and display it in one row?

Or do you want to take only one or two columns from one table in the database, and amalgamate these with one or two columns from another table in the database?  If so, which columns and which tables?

Or do you want to join data in a single row only if certain data in columns in both tables are identical?

Or something else?

Hello Errol

If you are interested in direct help, you can contact me here : http://calendarforum.dk/contact.html

As this is not really a X5 issue and as the forum is not suitable for assistance like this, I suggest you contact me or another person that will assist, directly.

As said; I have made this before :

here : http://eksempelsite.dk/udstillinger/Emner.php  two tables are joined and showed in a html-table.

Here : https://mogenshansen49.dk/det-gamle-testamente.html where the output is integrated in a X5 page. 

And - if your database can be accessed from outside your domain, it is as Paul earlier told, not very smart to expose connection settings.

Hello Paul,

This is the option I want.

Or do you want to take only one or two columns from one table in the database, and amalgamate these with one or two columns from another table in the database?  If so, which columns and which tables?

Here is what I am using now with no http 500 error.  But my results are 0. So something is logically incorrect.

<?php
$servername = "xxxxxx";
$username = "xxxxxxx";
$password = "xxxxxxx";
$dbname = "xxxxxxxxx";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT order_id,field_id, label, value, price, name FROM cartinvoice_addresses as table1
INNER JOIN cartproducts as table2 ON table1.order_id=table2.order_id
";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
// if( $row["field_id"] == "Address1"){ echo "<br><br>"; }
echo " " . $row["lable"].$row["value"].$row[price].$row[price];
//Added code

}
} else {
echo "0 results";
}
$conn->close();
?>

In addition here is what in the error log.

[08-Nov-2020 18:51:21 UTC] PHP Notice: Trying to get property 'num_rows' of non-object in /home/j8ge6f324p1c/public_html/php/test.php on line 20 [08-Nov-2020 18:56:04 UTC] PHP Notice: Trying to get property 'num_rows' of non-object in /home/j8ge6f324p1c/public_html/php/test.php on line 20 [08-Nov-2020 19:01:21 UTC] PHP Notice: Trying to get property 'num_rows' of non-object in /home/j8ge6f324p1c/public_html/php/test.php on line 20 [08-Nov-2020 19:07:17 UTC] PHP Notice: Trying to get property 'num_rows' of non-object in /home/j8ge6f324p1c/public_html/php/test.php on line 20 [08-Nov-2020 19:10:53 UTC] PHP Notice: Trying to get property 'num_rows' of non-object in /home/j8ge6f324p1c/public_html/php/test.php on line 20 [08-Nov-2020 19:20:16 UTC] PHP Notice: Trying to get property 'num_rows' of non-object in /home/j8ge6f324p1c/public_html/php/test.php on line 20

Is this what you are looking for, Errol?

<?php
$servername = "xxxxxx";
$username = "xxxxxx";
$password = "xxxxxx";
$dbname = "xxxxxx";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT order_id, field_id, label, value FROM cartinvoice_addresses INNER JOIN cartproducts USING (order_id);";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
// if( $row["field_id"] == "Address1"){ echo "<br><br>"; }
echo " " . $row["lable"].$row["value"].$row[price].$row[price];
//Added code

}
} else {
echo "0 results";
}
$conn->close();
?>

Paul,

I tried that, it's showing me the two fields from cartproducts price and name

echo " " . $row["lable"].$row["value"].$row[price].$row[name];

I tried that, it's not showing me the two fields from cartproducts price and name

echo " " . $row["lable"].$row["value"].$row[price].$row[name];

One other thing. It's not giving me the line break if field_id==Address1 is true.

What do you want it to show?  Can you be as specific as possible, please?  Can you give the names of the columns that you want from the first table (which I assume is cartinvoice_addresses) along with the names of the columns that you want from the second table (assumed to be cartproducts).

Update I fix the price and name issue. So now it's just the line break thing.

The line in question seems to be commented out:

// if( $row["field_id"] == "Address1"){ echo "<br><br>"; }

If you remove the two forward slashes from the start of the line that should sort it.

So... from cartinvoice_addresses I want to show label and value. From cartproducts I want to show price and name. And every instance of field_id=Address1, I need line break

I found the problem with the line break. I had the code commented out //.

Hello Paul, Thanks a bunch. It works.