Absolute Beginner SQL connection

1

Firstly, Thank you guys for MODX!!! This is an amazing platform! I have had my run in with word press, so I have come to realize how versatile MODX is.

Now, to get down to bussiness.

I am a absolute newb when it comes to php, html, css etc etc. I am learning via the trusted “Google, copy code, adapt, test” method lol.

So currently, I have designed a table in my modx db with myphpadmin. It shows up perfectly when I use dbAdmin.

Now I would like to create a resource, which connects to the custom table, and displays the records inside.

What i have tried is

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
/* $link = mysqli_connect("www.iceqrcodes.co.za", "iceqrcod_modx103", "password", "iceqrcod_modx103"); */

$link = mysqli_connect("localhost", "iceqrcod_modx103", "password", "iceqrcod_modx103"); 
// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
 
// Attempt insert query execution
//$sql = "INSERT INTO tbl_clients (first_name, last_name, email) VALUES ('Peter', 'Parker', 'peterparker@mail.com')";
$sql = "INSERT INTO `tbl_clients` (`First_Name`, `Surname`, `RSA_ID`, `Email`, `Cellphone`, `Home_Telephone`, `Work_Telephone`, `Home_Address_Line1`, `Home_Address_Line2`, `Home_Address_Line3`, `Postal_Address_Line1`, `Postal_Address_Line2`, `Postal_Address_Line3`, `Postal_Address_Suburb`, `Postal_Address_City`, `Postal_Address_PostalCode`, `Blood_Type`, `Allergies`, `Allergie1`, `Allergie2`, `Allergie3`, `Chronic_Medical_Conditions`, `Chronic_Medical_Condition_1`, `Chronic_Medical_Condition_2`, `Chronic_Medical_Condition_3`, `Chronic_Medical_Condition_4`, `Chronic_Medicine_1`, `Chronic_Medicine_2`, `Chronic_Medicine_3`, `Chronic_Medicine_4`, `Chronic_Medicine_5`, `Chronic_Medicine_6`, `Chronic_Medicine_7`, `Chronic_Medicine_8`, `Medical_Aid`, `Medical_Aid_name`, `Medical_Aid_option`, `Medical_Aid_number`, `House_Doctor_Name`, `House_Doctor_Telephone`, `Specialist_Doctor_Name`, `Specialist_Doctor_Telephone`, `Preferred_Hospital`, `In_Case_Of_Emergency_Contact1_Name`, `In_Case_Of_Emergency_Contact1_Number`, `In_Case_Of_Emergency_Contact2_Name`, `In_Case_Of_Emergency_Contact2_Number`, `Next_of_Kin_Name`, `Next_of_Kin_Number`, `How_Many_Vehicles`, `Vehicle_1_Registration`, `Vehicle_1_Make`, `Vehicle_1_Model`, `Vehicle_1_Color`, `Vehicle_2_Registration`, `Vehicle_2_Make`, `Vehicle_2_Model`, `Vehicle_2_Color`, `Vehicle_3_Registration`, `Vehicle_3_Make`, `Vehicle_3_Model`, `Vehicle_3_Color`, `Organ_Donor`, `Resuscitate`) VALUES ('Andre', 'Gouws', '7710185132088', 'andregouws@ymail.com', '0828901592', '0828901592', '0828901592', '494 Daphne Avenue', 'Mountainview', 'Pretoria', '494 Daphne Avenue', 'Mountainview', 'Hercules', 'Hecrules', '0001', '0001', 'A+', 'Yes', 'Bullshit', 'Wasting Time', 'idiots', 'Yes', 'Asma', 'Diabetes', 'Cholesterol', 'Hypertension', 'somethingsomethingElse', 'another something', 'also something', 'another pill', 'some fluid', 'pill', 'some ointyment', 'pilllll', 'yes', 'Hosmed', 'Plus', '90735313', 'JPJ Niemandt', '0123351245', 'Some Doctor', '0123456789', 'Eugene Marias', 'Bianca', '0844687234', 'Ieamna Anders', '0123456789', 'Bianca', '084 468 7234', '3', 'BY01KTGP', 'BMW', 'GS1200A', 'Red', 'TYT348GP', 'Opel', 'Corsa', 'White', 'susanjvrGP', 'Ford', 'Eco Sport', 'Green', 'Yes', 'No')"





if(mysqli_query($link, $sql)){
    echo "Records inserted successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
 
// Close connection
mysqli_close($link);

All I get, is blank page, no connection error, no record inserted, nothing.

the above is saved in a snippet, and then called on resource using [[testinsert]]

can anyone spare the time to assist me please?

1 Like
2

Might be worth looking at Rowboat but before you do - it is an old snippet so there might be better solutions out there now that other people on here are more aware of: https://docs.modx.com/extras/revo/rowboat

2 Likes
3

you don’t need to create your own connection to the db, if your table is in the same db as modx
you could just use a modx->query in your snippet

https://docs.modx.com/xpdo/2.x/class-reference/xpdo/xpdo.query

but there are ready solutions, like rowboat. So you don’t need to create your own snippet.

you could also go with a proper xpdo-schema and use for example migxLoopCollection to show the items. If you are planning todo more with your table than just listing the records.

1 Like
4

Thank you Bruno.
No idea how to use xpdo, or even Rowboat. I am trawling through page after after page of technical stuff, which is mind numbing. All i need, is a way to

  1. Connect to my custom table, tbl_clients
  2. Display the records inside.

Do i create a Snippet? A Code Chunk? do i Install a add on? How do i invoke the snippet? []? what do i need to put on the page to display the results?

if all that fails, a youtube video?

5

how do i display the records from rowboat? I am getting only a blank page. What is the php or html to display the results?

6

It might be worth saying that I am not doing it on a localhost, but directly in the manager?

7

i at least got a “Failed to connect to MySQL: Access denied for user ‘MemberA’@‘localhost’ (using password: YES)” message with the following code

$con=mysqli_connect("localhost","MemberA","","iceqrcod_modx103");
    // Check connection
    if (mysqli_connect_errno())
      {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }

    $result = mysqli_query($con,"SELECT * FROM tbl_clients");

    while($row = mysqli_fetch_array($result))
      {
      echo $row['FirstName'] . " " . $row['LastName']; //these are the fields that you have stored in your database table tbl_clients
      echo "<br />";
      }

    mysqli_close($con);
8

I would suggest moving your custom table into the MODX database, if that’s possible. That way, you’d already have a connection to it when MODX runs.

In addition, you could make your table xPDO friendly, which would allow you to use all the convenient xPDO query methods.

See this: Custom DB Tables.

2 Likes
9

Assuming your table is in the same database as MODX, you can use $modx->query like Bruno said. That returns a PDOStatement object (which is a PHP construct - not unique to MODX, search for “php pdo” to learn more about that) that you can use to iterate the table.

Tweaking your example to use the query method would look like this:

$stmt = $modx->query('SELECT * FROM tbl_clients');
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo $row['FirstName'] . ' ' . $row['LastName'] . ' <br />';
}

Few important things to note:

  • Snippets should always return instead of echo. You could adjust the example above to write to a $results variable that you return at the end, for example.
  • Earlier you showed you were calling the snippet cached. Make sure you don’t need it uncached.
1 Like