Solved

Help with sql query line

Posted on 2004-04-17
8
171 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

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
 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

813 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

11 Experts available now in Live!

Get 1:1 Help Now