"Automated MYSQL INSERT"

cry of war · October 16, 2007

Hi I am trying to make a web page for my admins for my web site. In this web site they have the option to add users and such to a table but they also can add user information

_______________

/ ID \/ Name \/ Age \

| 1 | Blah | 18 |

| 2 | Blah | 15 |

| 3 | Blah | 20 |

| 4 | Blah | 63 |

/ ID \/ Name \/ Age \/ Sex\

| 1 | Blah | 18 | F |

| 2 | Blah | 15 | M |

| 3 | Blah | 20 | M |

| 4 | Blah | 63 | F |

Now this isn’t to hard for me to do just coding by myself but when I have the admins adding stuff I may not catch the extra column that would throw this php script off.

<?PHP
$table1="affinityalignment";
?>
<head>
<title></title>
</head>
<body>
<table>
<?php 
$result1 = mysql_query("SELECT * FROM $table1 ");
ECHO "<TR>";
while ($row = mysql_fetch_array($result1, MYSQL_BOTH)) {
    printf("<td>$row[1]</td>");
}
ECHO "</TR>"; 
$max = mysql_query("SELECT COUNT(Name) FROM $table1");
$result2 = mysql_query("SELECT * FROM $table1");
$incrol = "0";
while ($row = mysql_fetch_array($result2, MYSQL_BOTH)) {
    printf("<td>$row[1]:</td>");
while ($incrol <= "$max") {
if ($incro1 == "0") {
echo "\n<tr>\n";
} 
$incro1++;
$number1++;
    printf("<input type='text' value='$row[$number]' name='$row[1]'>\n");
$base1++;
if ($incro1 == "$max") {
echo "</tr>";
$incro1="0";
}
if ($incro1 => "1") {
echo "</tr>";
}
}
}
?>

What I am asking is there a way to set this up so it automatically inserts all the above information into the field its describing without having to recode the mysql query insert? The feild is inserted by what ever is on the table already so if one of my admins adds a new column the said insert would have to be reconfigured and I wont always be there to fix it if you know what I mean.

trq · October 16, 2007

An easier way would be to have two tables.

CREATE TABLE users (
  id INT(15) NOT NULL PRIMARY KEY auto_increment,
  name VARCHAR(80) NOT NULL
);

CREATE TABLE user_details (
  id INT(15) NOT NULL PRIMARY KEY auto_increment,
  user_id INT(15) NOT NULL,
  key VARCHAR(80),
  val VARCHAR(80)
);

This way you can dynamically add users detail (keys / values) without needing to modify your table structure. Example...

INSERT INTO users (name) VALUES ('thorpe');
INSERT INTO user_details (user_id,key,val) VALUES (1,'age','32');
INSERT INTO user_details (user_id,key,val) VALUES (1,'sex','M');
INSERT INTO user_details (user_id,key,val) VALUES (1,'hobbies','music');
INSERT INTO user_details (user_id,key,val) VALUES (1,'hobbies','computers');

Now to get all the hobbies that user (thorpe) likes....

SELECT val FROM user_details WHERE key = 'hobbies' && user_id = 1;

The only real problem with this solution is the fact that the val filed is varchar, so you can't do any math on it.

cry of war · October 16, 2007

I get this error when trying to query that

CREATE TABLE user_details(

id INT( 15 ) NOT NULL PRIMARY KEY AUTO_INCREMENT ,
user_id INT( 15 ) NOT NULL ,
KEY VARCHAR( 80 ) ,
val VARCHAR( 80 ) 
);



MySQL said:  

#1064 - 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 'VARCHAR(80),
  val VARCHAR(80)
)' at line 4

trq · October 16, 2007

Sorry, key is actually a reserved word. Try...

CREATE TABLE user_details (
  id INT( 15 ) NOT NULL PRIMARY KEY AUTO_INCREMENT ,
  user_id INT( 15 ) NOT NULL ,
  `key` VARCHAR( 80 ) ,
  val VARCHAR( 80 ) 
);

cry of war · October 16, 2007

sorry for all the questions just never used this type of database handleing before but now im getting an error on your inserts

Error
SQL query: 

INSERT INTO user_details( user_id, 
KEY , val ) 
VALUES ( 1, 'age', '32' ) ;



MySQL said:  

#1064 - 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 'key,val) VALUES (1,'age','32')' at line 1

MadTechie · October 16, 2007

@cry of war

change key to `key`

Sorry, key is actually a reserved word. Try...

CREATE TABLE user_details (
  id INT( 15 ) NOT NULL PRIMARY KEY AUTO_INCREMENT ,
  user_id INT( 15 ) NOT NULL ,
  `key` VARCHAR( 80 ) ,
  val VARCHAR( 80 ) 
);

cry of war · October 16, 2007

i did that was the insert to put information into the database the database was created with 'key' although now its not letting me veiw the structure of it might have to put something in it first though.

Edit: i also tried for inserts

INSERT INTO user_details (user_id,'key',val) VALUES (1,'age','32');

INSERT INTO user_details (user_id,key,val) VALUES (1,'age','32');

neither of which would work

MadTechie · October 16, 2007

backticks(`) not quotes(')

Top left of the keyboard (or copy and paste)

cry of war · October 16, 2007

that could be why half my codes dont work

cry of war · October 16, 2007

ok got a little bit off topic here i still need to make it so I can make a insert without haveing to remake the code for it. I would try to use $_post but the out come of $_post is unprodictable because I wont be making it so i cant set up $blah=$_post['random'] because random is always changing

MadTechie · October 16, 2007

Use thorpe's post, but key should be `key`

An easier way would be to have two tables.
CREATE TABLE users (
  id INT(15) NOT NULL PRIMARY KEY auto_increment,
  name VARCHAR(80) NOT NULL
);

CREATE TABLE user_details (
  id INT(15) NOT NULL PRIMARY KEY auto_increment,
  user_id INT(15) NOT NULL,
  key VARCHAR(80),
  val VARCHAR(80)
);
This way you can dynamically add users detail (keys / values) without needing to modify your table structure. Example...
INSERT INTO users (name) VALUES ('thorpe');
INSERT INTO user_details (user_id,key,val) VALUES (1,'age','32');
INSERT INTO user_details (user_id,key,val) VALUES (1,'sex','M');
INSERT INTO user_details (user_id,key,val) VALUES (1,'hobbies','music');
INSERT INTO user_details (user_id,key,val) VALUES (1,'hobbies','computers');
Now to get all the hobbies that user (thorpe) likes....
SELECT val FROM user_details WHERE key = 'hobbies' && user_id = 1;
The only real problem with this solution is the fact that the val filed is varchar, so you can't do any math on it.

cry of war · October 16, 2007

i know what your getting at but its still not what im looking for. in order to insert data into a database I need a mysql_query("insert into table values blah, blah, blah, blah, blah ") right. But as my admins add more stuff this insert is going to need to be changed among 124 webpages every time they add something. because a input field is created from the database where a value is input so one time i may only need 1 thing to insert but another i may need thousands(hopes it never gets to that point bandwidth usage would be horrible) but i cant always sit here and edit 124 webpages every time some one add ones thing........

here is an example of why i need this the one web page is affected by the database which is effected by every other webpage including this one


<body>
<?php
ini_set('display_errors', '1');
error_reporting(E_ALL);
include "databaseconnect.php";
?>
</body>
id
name
discription
strength against what other affinity
weakness against what other affinity
attruibutes plus
<?php
$table1= "affinity";
$table2= "affinity";
$table3= "affinity";
$table4= "affinity";
?>
<head>
<title></title>
</head>
<body>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<h3>key:</h3>
numirical only = *
<br />
letters only = ~~
<br />
free for all = ~*~
<br />
<table>
<tr>
<td>Name:</td><td><input type="text" value="" name="name"></td>
</tr>
<tr>
<td>Discription:</td><td><textarea value="" name="discription" width="100px"></textarea></td>
</tr>
Alignment of Power:<br />
<table>
<?php
$incro1="0";
$base1="0";
$result1 = mysql_query("SELECT * FROM $table1");
while ($row = mysql_fetch_array($result1, MYSQL_BOTH)) {
if ($incro1=="0") {
echo "\n<tr>\n";
} 
$incro1++;
   printf("<td>$row[Name]:</td><td><input type='text' value='0' size='3' name='$row[Name]'></td>\n");
$base1++;
if ($incro1=="3") {
echo "</tr>";
$incro1="0";
}
}
if ($incro1 > 0) {
echo "</tr>";
}
?>
</table>
Bonuses:<br />
PRIMARY SKILLS:
<table>
<?php
$incro2="0";
$base2="0";
$result2 = mysql_query("SELECT * FROM  $table2");
while ($row = mysql_fetch_array($result2, MYSQL_BOTH)) {
if ($incro2=="0") {
echo "\n<tr>\n";
} 
$incro2++;
   printf("<td>$row[Name]:</td><td><input type='text' value='0' size='3' name='$row[Name]'></td>\n");
$base2++;
if ($incro2=="3") {
echo "</tr>";
$incro2="0";
}
}
if ($incro2 > 0) {
echo "</tr>";
}
?>
</TABLE>
SECONDARY SKILLS:
<table>
<?php
$incro3="0";
$base3="0";
$result3 = mysql_query("SELECT * FROM $table3");
while ($row = mysql_fetch_array($result3, MYSQL_BOTH)) {
if ($incro3=="0") {
echo "\n<tr>\n";
} 
$incro3++;
   printf("<td>$row[Name]:</td><td><input type='text' value='0' size='3' name='$row[Name]'></td>\n");
$base3++;
if ($incro3=="3") {
echo "</tr>";
$incro3="0";
}
}
if ($incro3 > 0) {
echo "</tr>";
}
?>
</TABLE>
STATUS BONUS:
<table>
<?php
$incro4="0";
$base4="0";
$result4 = mysql_query("SELECT * FROM $table4"); 
while ($row4 = mysql_fetch_array($result4, MYSQL_BOTH)) {
if ($incro4=="0") {
echo "\n<tr>\n";
} 
$incro4++;
   printf("<td>$row4[Name]:</td><td><input type='text' value='0' size='3' name='$row4[Name]'></td>\n");
$base4++;
if ($incro4=="3") {
echo "</tr>";
$incro4="0";
}
}
if ($incro4 > 0) {
echo "</tr>";
}
?>
<?php
echo "$row4";
if (addaffinity) {
mysql_query("INSERT INTO ??? VALUES ???,???,???")
//PART OF THE CODING I DONT GET BECAUSE ITS ALWAYS NEVER THE SAME
?>
</TABLE>
<input type="submit" value="UPDATE" name="addaffinity">
</form>
</body>

The code works great i just need to find a way to insert this all so it can be accessed the same way at a letter time with no coding for me = )

MadTechie · October 16, 2007

thorpes idea, is sound,

i don't really understand why you think it, won't suite!

anyways i'm Going to lunch..

will check back on my return

cry of war · October 16, 2007

lol because $row = mysql_fetch_array($result1, MYSQL_BOTH) wont work with that right will it??? and if it does how do you still set up a dynamic insert for it

time to go to lunch be back in ten

Sign In

"Automated MYSQL INSERT"

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information