Solved

Help with sql query line

Posted on 2004-04-17
8
170 Views
Last Modified: 2008-03-06
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
Comment
Question by:farhadabas
  • 4
  • 3
8 Comments
 
LVL 12

Accepted Solution

by:
venkateshwarr earned 100 total points
ID: 10850912
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
 

Author Comment

by:farhadabas
ID: 10850960
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
 
LVL 5

Expert Comment

by:TheClickMaster
ID: 10852190
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
 

Author Comment

by:farhadabas
ID: 10853180
roger and one question what does .= mean? like when u said $sql2.=? does it add to the end of text or something?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 5

Expert Comment

by:TheClickMaster
ID: 10853383
.= 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
 
LVL 5

Expert Comment

by:TheClickMaster
ID: 10853395
Oh, sorry not + but .

$sql2 = $sql2 . ",''";
0
 

Author Comment

by:farhadabas
ID: 10853474
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
 
LVL 5

Expert Comment

by:TheClickMaster
ID: 10853617
No problem I dont want to steal his points ;)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now