• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

Help with sql query line

hello,
i am making a website where one of the tables will sometimes get new columns in it. The problem is that i have these lines:

$sql2 = "INSERT INTO la_members values ('','$_POST[username]','$_POST[pass]','$_POST[msn]','$_POST[email]', '0')";


which ofcourse gets the info from a form in another page. Well since i will get new columns i dont know how to make it so i can add to the end of $sql2 like this:

$sql2 = "INSERT INTO la_members values ('','$_POST[username]','$_POST[pass]','$_POST[msn]','$_POST[email]', '0','','','','','','','','','','')";

As many as i want to. Oh yea i have another table where it holds the number of columns that i need. But i somehow i need to be able to add to the end of $sql.


When i mean add i mean like it checks how many i need to add from the other table and like have a loop or something and add the number of '' i need at the end of $sql2.

Help Appreciated thanks
0
farhadabas
Asked:
farhadabas
  • 4
  • 3
1 Solution
 
venkateshwarrCommented:
If you know the field names in the table.... you can just say

$sql2 = "INSERT INTO la_members(username,pass,msn,email,other) values ('$_POST[username]','$_POST[pass]','$_POST[msn]','$_POST[email]', '0')";

username,pass,msn,email,other are the actual field names in the database to which you have to assign the values...




OR
you can find the number of fields in the table using mysql_num_fields() function:

Here is a snippet:
<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
$fields = mysql_list_fields("database1", "table1", $link);
$columns = mysql_num_fields($fields);

for ($i = 0; $i < $columns; $i++) {
   echo mysql_field_name($fields, $i) . "\n";
}
?>

So before inserting the values in the database,
find the number of columns and simply attach '' items in the INSERT query.
0
 
farhadabasAuthor Commented:
umm i am not sure if you understood the problem. Ok i am making a game website. One table has the number of games. But as an admin i will be able to add more games so the number of games in that table will increase. Well the when a new member signs up i need to use insert to add him to the members table. But in this members table at the end it has 1 column for each game. Well i want code to automaticly count the number of rows in the table that holds the names of the games. Lets say we assign it to $gamenumber well then i want so when i add members it looks at the value of $gamenumber and adds that many '' to the end of:

$sql2 = "INSERT INTO la_members(username,pass,msn,email,other) values ('$_POST[username]','$_POST[pass]','$_POST[msn]','$_POST[email]', '0')";


so if there is one:

$sql2 = "INSERT INTO la_members(username,pass,msn,email,other) values ('$_POST[username]','$_POST[pass]','$_POST[msn]','$_POST[email]', '0','')";

two:

$sql2 = "INSERT INTO la_members(username,pass,msn,email,other) values ('$_POST[username]','$_POST[pass]','$_POST[msn]','$_POST[email]', '0','','')";

and so on

so i want it somehow to add to the end of $sql2 insert query so it does not give me and error that in the table there are more columns than u specified in $sql2

So what i am asking is somehow when i insert a member into the tables the number of columns specified in the $sql2 query match the number in the actuall table. I want it somehow add to the end of string or something.
0
 
TheClickMasterCommented:
As venkateshwarr said, simply get the  number of columns then do a loop and here is the code:

//First a query with no games at all
$sql2 = "INSERT INTO la_members(username,pass,msn,email,other) values ('$_POST[username]','$_POST[pass]','$_POST[msn]','$_POST[email]', '0'";

//somehow you have to get the number of games into the variable $columns
//maybe look at venkateshwarr's answer again

// add ,'' for each game
for($i = 0; $i < $columns; $i++)
$sql2 .= ",''";

// add the ending parenthesis
$sql2 .=");";

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
farhadabasAuthor Commented:
roger and one question what does .= mean? like when u said $sql2.=? does it add to the end of text or something?
0
 
TheClickMasterCommented:
.= means take the existing string and add the new part to the end.

You could also write the following to have the exact same effect:

$sql2 = $sql2 + ",''";

The only difference is that .= takes less space and looks cleaner =)


0
 
TheClickMasterCommented:
Oh, sorry not + but .

$sql2 = $sql2 . ",''";
0
 
farhadabasAuthor Commented:
thanks very much click i wish i really could give u both points but now i realized how ven answered my question and i didnt realize he did. Thanks clickmaster you always help me but ven. i realize both of you deserve it but ven got it first.
0
 
TheClickMasterCommented:
No problem I dont want to steal his points ;)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now