PHP And MySQL

If you have any suggestions or ideas about improving Salix, here's the place to post them.
Post Reply
lurker666
Posts: 32
Joined: 24. Apr 2023, 09:42

PHP And MySQL

Post by lurker666 »

My question is, what do I need to install to use PHP with MySQL properly under Linux? I can program in PHP, but the other parts [MySQL, installing dependencies] never interested me, so I always just looked it up on the internet.

The problem is that when I request a value [username, password] from a user, it is not transferred to the database [INSERT INTO users WHERE etc...]. I can connect to the database via PHP, but the values are not sent. I have written several parts in PHP that I need, and it is holding me back in my whole project that it is not sending the data to MySQL.

Can anyone help me with this? The question does not necessarily apply only to Salix, but more generally.
lurker666
Posts: 32
Joined: 24. Apr 2023, 09:42

Re: PHP And MySQL

Post by lurker666 »

By the way, I hope you are well! :D
djemos
Salix Warrior
Posts: 1442
Joined: 29. Dec 2009, 13:45
Location: Greece

Re: PHP And MySQL

Post by djemos »

I think you have syntax error in sql statement so failed to insert the records.

Here is a php file which has been tested and it is working.
First have to create database Animal and grant privileges
from a terminal type.

Code: Select all

mysql -u root -p
give the password and will go to mysql>

create database Animal;
CREATE USER 'djemos'@'localhost' IDENTIFIED BY 'djemos';
GRANT ALL PRIVILEGES ON * . * TO 'djemos'@'localhost';
flush privileges;
Here is a php file which has been tested and it is working. Save it to the path of httpd (/var/www/htdocs/) with name test.php fore example then type in firefox
http://localhost/test.php
every time running it a new record will be added to database and display all records.

Code: Select all

<?php
           		$dbhost = 'localhost';
           		$dbuser = 'djemos';
          		$dbpass = 'djemos';
          	        $dbname = 'Animal';
			$tablename = 'pet';
			$orderby = "1 DESC LIMIT 500";  // column # to sort & max # of records to display          
			$conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
			// Check connection
			if (!$conn) {
				die("Connection failed: " . mysqli_connect_error());
				}
			echo "Connected successfully" . "<br>";
						
			$sql= "CREATE TABLE  IF NOT EXISTS $tablename (name VARCHAR(20), sex CHAR(1), color VARCHAR(20)) ";
			if (mysqli_query($conn, $sql)) {
			echo "New table created successfully" . "<br>";
				} else {
				echo "Error: " . $sql . "<br>" . mysqli_error($conn);
			}
			
			$sql = "INSERT INTO $tablename (name, sex, color) VALUES ('DOG' , 'm', 'BLACK')";
			if (mysqli_query($conn, $sql)) {
			echo "New record created successfully". "<br>";
				} else {
				echo "Error: " . $sql . "<br>" . mysqli_error($conn);
			}

			// Run query & verify success
			$sql = "SELECT * FROM {$tablename} ORDER BY {$orderby}";
			if ($result = $conn->query($sql)) {
				$conn->close();  // Close table
				$fields_num = $result->field_count;
				$count_rows = $result->num_rows;

			if ($count_rows == 0) {
				die ("No data found in table: [" . $tablename . "]" );  //quit 
			} 
			} else {
			$conn->close();  // Close table
			die ("Error running SQL:<br>" . $sql );  //quit
		}

		// Start drawing table
		echo "<!DOCTYPE html><html><head><title>{$tablename}</title>";
		echo "<style> table, th, td { border: 1px solid black; border-collapse: collapse; }</style></head>";
		echo "<body><span style='font-size:18px'>Table: <strong>{$tablename}</strong></span><br>";  
		echo "<span style='font-size:10px'>({$count_rows} records, {$fields_num} fields)</span><br>";
		echo "<br><span style='font-size:10px'><table><tr>";        

		// Print table Field Names
		while ($finfo = $result->fetch_field()) {
			echo "<td><center><strong>{$finfo->name}</strong></center></td>";
			}
		echo "</tr>"; // Finished Field Names

		/* Loop through records in object array */
		while ($row = $result->fetch_row()) {
			echo "<tr>";    // start data row
			for( $i = 0; $i<$fields_num; $i++ ) {
				echo "<td>{$row[$i]}</td>";
			}
		echo "</tr>";   // end data row
		}

		echo "</table>";  // End table
		$result->close();  // Free result set		
		mysqli_close($conn);
?>     

lurker666
Posts: 32
Joined: 24. Apr 2023, 09:42

Re: PHP And MySQL

Post by lurker666 »

djemos wrote: 3. Nov 2023, 18:21 I think you have syntax error in sql statement so failed to insert the records.

Here is a php file which has been tested and it is working.
First have to create database Animal and grant privileges
from a terminal type.

Code: Select all

mysql -u root -p
give the password and will go to mysql>

create database Animal;
CREATE USER 'djemos'@'localhost' IDENTIFIED BY 'djemos';
GRANT ALL PRIVILEGES ON * . * TO 'djemos'@'localhost';
flush privileges;
Here is a php file which has been tested and it is working. Save it to the path of httpd (/var/www/htdocs/) with name test.php fore example then type in firefox
http://localhost/test.php
every time running it a new record will be added to database and display all records.

Code: Select all

<?php
           		$dbhost = 'localhost';
           		$dbuser = 'djemos';
          		$dbpass = 'djemos';
          	        $dbname = 'Animal';
			$tablename = 'pet';
			$orderby = "1 DESC LIMIT 500";  // column # to sort & max # of records to display          
			$conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
			// Check connection
			if (!$conn) {
				die("Connection failed: " . mysqli_connect_error());
				}
			echo "Connected successfully" . "<br>";
						
			$sql= "CREATE TABLE  IF NOT EXISTS $tablename (name VARCHAR(20), sex CHAR(1), color VARCHAR(20)) ";
			if (mysqli_query($conn, $sql)) {
			echo "New table created successfully" . "<br>";
				} else {
				echo "Error: " . $sql . "<br>" . mysqli_error($conn);
			}
			
			$sql = "INSERT INTO $tablename (name, sex, color) VALUES ('DOG' , 'm', 'BLACK')";
			if (mysqli_query($conn, $sql)) {
			echo "New record created successfully". "<br>";
				} else {
				echo "Error: " . $sql . "<br>" . mysqli_error($conn);
			}

			// Run query & verify success
			$sql = "SELECT * FROM {$tablename} ORDER BY {$orderby}";
			if ($result = $conn->query($sql)) {
				$conn->close();  // Close table
				$fields_num = $result->field_count;
				$count_rows = $result->num_rows;

			if ($count_rows == 0) {
				die ("No data found in table: [" . $tablename . "]" );  //quit 
			} 
			} else {
			$conn->close();  // Close table
			die ("Error running SQL:<br>" . $sql );  //quit
		}

		// Start drawing table
		echo "<!DOCTYPE html><html><head><title>{$tablename}</title>";
		echo "<style> table, th, td { border: 1px solid black; border-collapse: collapse; }</style></head>";
		echo "<body><span style='font-size:18px'>Table: <strong>{$tablename}</strong></span><br>";  
		echo "<span style='font-size:10px'>({$count_rows} records, {$fields_num} fields)</span><br>";
		echo "<br><span style='font-size:10px'><table><tr>";        

		// Print table Field Names
		while ($finfo = $result->fetch_field()) {
			echo "<td><center><strong>{$finfo->name}</strong></center></td>";
			}
		echo "</tr>"; // Finished Field Names

		/* Loop through records in object array */
		while ($row = $result->fetch_row()) {
			echo "<tr>";    // start data row
			for( $i = 0; $i<$fields_num; $i++ ) {
				echo "<td>{$row[$i]}</td>";
			}
		echo "</tr>";   // end data row
		}

		echo "</table>";  // End table
		$result->close();  // Free result set		
		mysqli_close($conn);
?>     

Thank you for your answer, I will save the configs. But, if I try to access to mysql, I get the following lines:

Code: Select all


ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2)

What can I do?
djemos
Salix Warrior
Posts: 1442
Joined: 29. Dec 2009, 13:45
Location: Greece

Re: PHP And MySQL

Post by djemos »

Did you install mariadb?
sudo slapt-get -i mariadb

Create the default mysql databases:
mysql_install_db --user=mysql

Start the mysql server:
sudo service restart mysqld

Create password for mysql root user:
/usr/bin/mysqladmin -u root password '??????????' (replace ???? with your password to use)

Start the mysql server:
sudo service restart mysqld

TEST Mysql
mysql -u root -p
SHOW databases;
QUIT


On /etc/httpd/httpd.conf
# Uncomment the following line to enable PHP:
#
Include /etc/httpd/mod_php.conf

Then restart apache
sudo service restart httpd

Check if httpd is running
ps ax |grep httpd
User avatar
flatdog
Posts: 6
Joined: 4. Nov 2023, 16:09
Location: Transylvania, Romania

Re: PHP And MySQL

Post by flatdog »

I would start with checking if mysqld is running, something like

Code: Select all

ps aux | grep mysqld
If it is up and running, I would try to connect using a TCP/IP connection (that means replacing "localhost" in your my.cnf file with "127.0.0.1").
If you need to connect via socket connector, check the permissions on the "/var/run/mysql/" directory. Should be owned by the user mysqld is running. I'm not running mysql on Salix, these hints are just that, hints. I hope this helps.
A typo in documentation can ruin your day.
lurker666
Posts: 32
Joined: 24. Apr 2023, 09:42

Re: PHP And MySQL

Post by lurker666 »

Sorry for the late reply, but what djemos wrote it worked, so now I can do what I want!

Thanks and have a nice day!
Post Reply