?
Solved

Help with sql query line

Posted on 2004-04-17
8
Medium Priority
?
175 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 12

Accepted Solution

by:
venkateshwarr earned 400 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
WordPress Tutorial 1: Installation & Setup

WordPress is a very popular option for running your web site and can be used to get your content online quickly for the world to see. This guide will walk you through installing the WordPress server software and the initial setup process.

 

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

WordPress Tutorial 2: Terminology

An important part of learning any new piece of software is understanding the terminology it uses. Thankfully WordPress uses fairly simple names for everything that make it easy to start using the software.

Question has a verified solution.

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

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

764 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