Pasing Values From A Php Script To A Sql Script

[bigjoe11a]

I didn't find a thing on this or I just use the wrong search string. Lets say I have 2 values on a HTML form that I need to send to the SQL Script that the PHP runs. If you under stood that. $db_name = $_POST['dbname']; //Database name$db_prefix = $_POST['dbprefix']; //prefix name How can I send these 2 values from the PHP form (HTML) to the SQL Script so that I can use them $db_name is the name of the database$db_prefix is the name of the prefix to add in front on the tables created Sample code could help a lot. Joe

[Synook]

You just put them in your script in the appropriate places:

<?php // ...mysql_select_db($db_name);// ...mysql_query("SELECT * FROM " . mysql_real_escape_string($db_prefix) . "_table ...");// ...?>

Or do you have a stored procedure?

[bigjoe11a]

So what your saying is that the variables I use in my PHP scripts I just add them into the sql script and that should do it. Let me show you what I think you mean.

CREATE DATABASE $settings['db_name'];;USE $settings['db_name'];/*Table structure for table `specificproblems` */CREATE TABLE `$settings['db_prefix}users` (  `id` int(11) default NULL,  `name` varchar(50) default NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Is that what you mean Joe

[Don E]

I think Synook means the values stored in the $_POST array that come from the HTML form, you can take those values and put them into a MySQL query so that you can store the values in a table/database. The example He gave you demonstrates how to do that:

mysql_query("SELECT * FROM " . mysql_real_escape_string($db_prefix) . "_table ...");

To create a database/tables, you can do it with phpmyadmin or however you're familiar with doing it; command console window(cmd/dos) in windows for example. (This is if you're using xampp or wamp).

[bigjoe11a]

Sorry Guys, That's not what I mean. Ok. I have a file called 'test.sql' I need to pass the values from the forum to that file. So that when PHP runs the script. The file uses the values and creates the right database and adds the prefix to each of the tables. Does that help, I hope so

[JamesB]

I don't think you can actually send data from php into an sql file. What method are you currently using to send the sql commands to the sql server? If you have to use the .sql file, you could make php fetch the sql file's contents, sprintf() the db name and db prefix into the string, split the string by ; to get the queries, then trim and run them, like:

function runFileSQL() {	 $args = func_get_args();	 $filePath = $args[0]; 	 $queries = file_get_contents($filePath);	 $args[0] = $queries;	 $queries = call_user_func_array('sprintf', $args); 	 $queries = explode(';', $queries);	 foreach ($queries as $query) {		 $query = trim($query);		 mysql_query($query);	 }} $db_name = $_POST['dbname']; //Database name$db_prefix = $_POST['dbprefix']; //prefix namerunFileSQL('test.sql', $db_name, $db_prefix);

and in the sql file:

CREATE DATABASE `%1$s`;USE `%1$s`;/*Table structure for table `specificproblems` */ CREATE TABLE `%2$susers` (`id` int(11) default NULL,`name` varchar(50) default NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;

[bigjoe11a]

Ok, this is what I have. Below is the PHP code I use to read and run the sql script.

if (isset($_POST['submitBtn'])){	    $host = isset($_POST['hostname']) ? $_POST['hostname'] : '';	    $user = isset($_POST['username']) ? $_POST['username'] : '';	    $pass = isset($_POST['password']) ? $_POST['password'] : '';	    	    $dbname = $_POST['dbname'];	    $dbpreix = $_POST['prefix'];	    	    	    $con = mysql_connect($host,$user,$pass);	    if ($con !== false){		   // Load and explode the sql file		   $f = fopen($sqlFileToExecute,"r+");		   $sqlFile = fread($f,filesize($sqlFileToExecute));		   $sqlArray = explode(';',$sqlFile);		   		   //Process the sql file by statements		   foreach ($sqlArray as $stmt) {			  if (strlen($stmt)>3){		      	  $result = mysql_query($stmt);		      	  if (!$result){		      		 $sqlErrorCode = mysql_errno();		      		 $sqlErrorText = mysql_error();		      		 $sqlStmt	  = $stmt;		      		 break;		      	  }		         }		   }	    }	    unset($_POST['submitBtn']);	  

and this is what my test.sql file looks like. I just need to pass the values from the PHP script to the test.sql script

SET NAMES utf8;SET SQL_MODE='';SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';CREATE DATABASE mapnet;USE mapnet;/*Table structure for table `specificproblems` */CREATE TABLE `domains` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `domain` varchar(50) NOT NULL,  `user` varchar(50) NOT NULL,  `password` varchar(50) NOT NULL,  `status` varchar(50) NOT NULL default 'Pending',  `active` varchar(50) NOT NULL default 'No',  `reason` varchar(50) default '',  PRIMARY KEY  (`id`));CREATE TABLE `files` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `username` varchar(50) NOT NULL default '',  `title` varchar(50) NOT NULL default '',  `filename` varchar(50) NOT NULL default '',  `maptype` varchar(50) NOT NULL default '',  `filesize` int(50) NOT NULL default 0,  `filetype` varchar(50) NOT NULL default '',  `category` varchar(50) NOT NULL default '',  `maprequire` varchar(50) NOT NULL default '',  `desx` text default '',  `image` varchar(150) default 'images/default.gif',  `postdat` datetime NOT NULL,  PRIMARY KEY  (`id`));CREATE TABLE `uploaders` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `alias` varchar(50) NOT NULL,  `real_name` varchar(50) NOT NULL,  `email` varchar(50) NOT NULL,  `details` varchar(50) NOT NULL default '',  `staus` varchar(50) NOT NULL default '',  `active` varchar(50) default '',  PRIMARY KEY  (`id`));CREATE TABLE `main_config` (  `config_name` varchar(255) NOT NULL default '',  `config_value` varchar(255) NOT NULL default '',  PRIMARY KEY  (`config_name`));CREATE TABLE `reg_config` (  `config_name` varchar(255) NOT NULL default '',  `config_value` varchar(255) NOT NULL default '',  PRIMARY KEY  (`config_name`));CREATE TABLE `links_config` (  `link_title` varchar(255) NOT NULL default '',  `link_value` varchar(255) NOT NULL default '',  PRIMARY KEY  (`link_title`));CREATE TABLE `users` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `user_name` varchar(50),  `user_pass` varchar(150),  `user_email` varchar(150),  `user_group` varchar(65),  `user_posts` int(11) default '0',  `user_topics` int(11) default '0',  `user_joined` int(20),  PRIMARY KEY  (`id`));CREATE TABLE `blocks` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `block_title` varchar(50),  `block_on` int(10),  `block_name` varchar(50),  `block_content` text CHARACTER SET binary NOT NULL,  `block_pos` varchar(50) default '',  PRIMARY KEY  (`id`));CREATE TABLE `category` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `cat_title` varchar(50) NOT NULL default '',  `cat_desc` text NOT NULL default '',  `cat_image` varchar(50) NOT NULL default 'default.gif',  PRIMARY KEY  (`id`));/*Data for the table `main_config` */insert  into `main_config`(`config_name`,`config_value`) values ('allow_avatar_local','0');insert  into `main_config`(`config_name`,`config_value`) values ('allow_avatar_remote','0');insert  into `main_config`(`config_name`,`config_value`) values ('allow_avatar_upload','0');insert  into `main_config`(`config_name`,`config_value`) values ('allow_bbcode','1');insert  into `main_config`(`config_name`,`config_value`) values ('allow_html','0');insert  into `main_config`(`config_name`,`config_value`) values ('allow_html_tags','b,i,u,pre');insert  into `main_config`(`config_name`,`config_value`) values ('allow_namechange','0');insert  into `main_config`(`config_name`,`config_value`) values ('allow_sig','1');insert  into `main_config`(`config_name`,`config_value`) values ('allow_smilies','1');insert  into `main_config`(`config_name`,`config_value`) values ('allow_theme_create','0');insert  into `main_config`(`config_name`,`config_value`) values ('avatar_filesize','6144');/*Data for the table `reg_config` */insert  into `reg_config`(`config_name`,`config_value`) values ('allowreg','0');insert  into `reg_config`(`config_name`,`config_value`) values ('allowcatchpha','0');/*Data for the table `links_config` */insert  into `links_config`(`link_title`,`link_value`) values ('Home','index.php');insert  into `links_config`(`link_title`,`link_value`) values ('Register','styles/templates/register.php');/*Data for the table `category` */insert  into `category`(`cat_title`,`cat_desc`,`cat_image`) values ('Delta Force Extreme','Missions for Delta Force Extreme','dfx.gif');insert  into `category`(`cat_title`,`cat_desc`,`cat_image`) values ('Delta Force Extreme 2','Missions for Delta Force Extreme 2','dfx2.gif');SET SQL_MODE=@OLD_SQL_MODE;

From the code. at the top I need to pass the $settings['db_name'] and the $settings['db_prefix'] to the sql script.

[JamesB]

in the sql file, you can change:

CREATE DATABASE mapnet;USE mapnet;

to

CREATE DATABASE %1$s;USE %1$s;

and add a %2$s before every table name that you want prefixed:

CREATE TABLE `%2$sdomains` (CREATE TABLE `%2$sfiles` (etc

then in the php file, before the explode() call, add:

$sqlFile = sprintf($sqlFile, $dbname, $dbprefix);

also you should run fclose() on $f after the fread().

[bigjoe11a]

I just making sure I under stand you. In my php script. Just before the

$sqlArray = explode(';',$sqlFile);

I would add this line

$sqlFile = sprintf($sqlFile, $setting['db_name'], $setting['db_prefix']);

and that's all I have to do.

[bigjoe11a]

Ok, I made the changes. I just can't test it yet until some of these other problems get resolved, I will let you know. I do have one more question for you. When i run the sql script. When I do a insert. How many options can I run when I run the script. what I was thinking was that when I insert into the database. I wanted to add like a default image into a field called 'user_avatar' and I wanted to know if I can do that in the SQL script or will I have just do that in PHP. If you don't mind. Do you know of a web site that has all the options for running a SQL Script. The idea is to load a file in and then insert that file into the table. I just wasn't sure if I can do things like that in a SQL Script.

[JamesB]

I just making sure I under stand you. In my php script. Just before the

$sqlArray = explode(';',$sqlFile); 

I would add this line

$sqlFile = sprintf($sqlFile, $setting['db_name'], $setting['db_prefix']);

and that's all I have to do.

yeh, assuming $setting['db_name'] and $setting['db_prefix'] are both set, and you also made the changes to the SQL file in my last post. you can see the INSERT syntax on the MySQL manual here: http://dev.mysql.com/doc/refman/5.6/en/insert.htmland all the "Data Manipulation Statements" here: http://dev.mysql.com/doc/refman/5.6/en/sql-syntax-data-manipulation.html I've never inserted binary data into a database, so i can't help you on that.But inserting the same default image into several rows sounds a bad idea, instead you could make the field optional with NULL, then when selecting data from the db you could check if the field is null and point the client to the default image, either stored once in the db or once in a folder outside the db.

[bigjoe11a]

Thank James. I just won't under stand all that. I'll keep it so I can referance to it if I have too, Thank you for your help

[bigjoe11a]

James it looks like it did work, and then it didn't. I keep getting an error of

yeh, assuming $setting['db_name'] and $setting['db_prefix'] are both set, and you also made the changes to the SQL file in my last post. you can see the INSERT syntax on the MySQL manual here: http://dev.mysql.com.../en/insert.htmland all the "Data Manipulation Statements" here: http://dev.mysql.com...nipulation.html I've never inserted binary data into a database, so i can't help you on that.But inserting the same default image into several rows sounds a bad idea, instead you could make the field optional with NULL, then when selecting data from the db you could check if the field is null and point the client to the default image, either stored once in the db or once in a folder outside the db.

James. I desided to try it, so I removed th database and then ran the installer. and the error below is what I keep getting, It seems like it's creating the database ok. It created the tables ok. The error I get is below along with the top part of my code. and below that you will find my php code. An error occured during installation!Error code: 1064Error text: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%1$s' at line 1Statement:

 SET NAMES utf8; SET SQL_MODE='';SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'; CREATE DATABASE %1$s; USE %1$s;/*Table structure for Mapnet */ CREATE TABLE `%2$sdomains` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `domain` varchar(50) NOT NULL,  `user` varchar(50) NOT NULL,  `password` varchar(50) NOT NULL,  `status` varchar(50) NOT NULL default 'Pending',  `active` varchar(50) NOT NULL default 'No',  `reason` varchar(50) default '',  PRIMARY KEY  (`id`));

PHP below

if (isset($_POST['submitBtn'])){		$host = isset($_POST['hostname']) ? $_POST['hostname'] : '';		$user = isset($_POST['username']) ? $_POST['username'] : '';		$pass = isset($_POST['password']) ? $_POST['password'] : '';				$dbname = $_POST['dbname'];		$prefix = $_POST['prefix'];						$con = mysql_connect($host,$user,$pass);		if ($con !== false){		   // Load and explode the sql file		   $f = fopen($sqlFileToExecute,"r+");		   $sqlFile = fread($f,filesize($sqlFileToExecute));		   fclose($f);		   runFileSQL($sqlFileToExecute, $dbname, $prefix);		   $sqlArray = explode(';',$sqlFile);		  		   //Process the sql file by statements		   foreach ($sqlArray as $stmt) {			  if (strlen($stmt)>3){					$result = mysql_query($stmt);					if (!$result){					   $sqlErrorCode = mysql_errno();					   $sqlErrorText = mysql_error();					   $sqlStmt	  = $stmt;					   break;					}				 }		   }		}		unset($_POST['submitBtn']);	  

[JamesB]

the idea is to either use the runFileSQL() function, or use your method of fetching and parsing the sql file contents, but not both methods at the same time.the runFileSQL() function will fetch and parse the data, as well as send the queries. also you should parse $f to fclose(), fclose() takes a file resource not a filename.

[bigjoe11a]

Sorry James. I thought you said to put your line (runFileSQL($sqlFileToExecute, $dbname, $prefix) just before the explode. So what did you want me to do.Should I take out the old code and just use yours or what. Can you tell me what to take out and what should stay.Here's the code again, below

if (isset($_POST['submitBtn'])){	    $host = isset($_POST['hostname']) ? $_POST['hostname'] : '';	    $user = isset($_POST['username']) ? $_POST['username'] : '';	    $pass = isset($_POST['password']) ? $_POST['password'] : '';	    	    $dbname = $_POST['dbname'];	    $prefix = $_POST['prefix'];	    	    	    $con = mysql_connect($host,$user,$pass);	    if ($con !== false){		   // Load and explode the sql file		   $f = fopen($sqlFileToExecute,"r+");		   $sqlFile = fread($f,filesize($sqlFileToExecute));		   fclose($f);		   runFileSQL($sqlFileToExecute, $dbname, $prefix);		   $sqlArray = explode(';',$sqlFile);		   		   //Process the sql file by statements		   foreach ($sqlArray as $stmt) {			  if (strlen($stmt)>3){		      	  $result = mysql_query($stmt);		      	  if (!$result){		      		 $sqlErrorCode = mysql_errno();		      		 $sqlErrorText = mysql_error();		      		 $sqlStmt	  = $stmt;		      		 break;		      	  }		         }		   }	    }	    unset($_POST['submitBtn']);	   

[JamesB]

to use your method for fetching the SQL file data and sending the queries, in your latest code, change this line:

runFileSQL($sqlFileToExecute, $dbname, $prefix);

to this:

$sqlFile = sprintf($sqlFile, $dbname, $prefix);

or if you want to use the runFileSQL() function, in your latest code, change this code:

// Load and explode the sql file$f = fopen($sqlFileToExecute,"r+");$sqlFile = fread($f,filesize($sqlFileToExecute));fclose($f);runFileSQL($sqlFileToExecute, $dbname, $prefix);$sqlArray = explode(';',$sqlFile); //Process the sql file by statementsforeach ($sqlArray as $stmt) {if (strlen($stmt)>3){$result = mysql_query($stmt);if (!$result){$sqlErrorCode = mysql_errno();$sqlErrorText = mysql_error();$sqlStmt = $stmt;break;}}}

to this:

runFileSQL($sqlFileToExecute, $dbname, $prefix);

[bigjoe11a]

Thanks James, I'm using you code and it work on the 1st try, I owe you one. Joe

[Synook]

Comment: you can also just have your "SQL script" run through the PHP interpreter, either using the server configuration, or by just giving it the .php extension.

[bigjoe11a]

Sorry, Synook, That's over my head. All this crap I should have learn this 2 years ago. I just started getting into advance PHP in the last week. If you can give me some comments and samples on what your talking about would help. ThanksJoe