Solved

How to alter table with php

Posted on 2007-11-29
6
1,372 Views
Last Modified: 2013-12-12
What's wrong with this:

$profilename = 'photographers';
$name = 'Gender';
$type = 'varchr(255)';

$result = mysql_query("ALTER TABLE '$profilename' ADD COLUMN '$name' '$type'") or die(mysql_error());

It says:

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 ''' ADD COLUMN 'Gender' 'varchar(255)'' at line 1
0
Comment
Question by:jtcy
6 Comments
 
LVL 20

Expert Comment

by:steelseth12
ID: 20372906

$profilename = 'photographers';

$name = 'Gender';

$type = 'varchar(255)';
 

$result = mysql_query("ALTER TABLE `$profilename` ADD COLUMN `$name` $type") or die(mysql_error());

Open in new window

0
 
LVL 20

Accepted Solution

by:
steelseth12 earned 350 total points
ID: 20372913
use backticks `` on entities (tables, columns) not single columns '
Also the datatype is varchar not varchr
0
 

Author Comment

by:jtcy
ID: 20372980
Whats wrong with this:

$result = mysql_query("INSERT INTO profile_fields (`id`, `name`, `profile_id`, `type_id`, `order`) VALUE
  ('0', '$name', '$profileid', '$type', '$order'") or die(mysql_error());
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 27

Expert Comment

by:yodercm
ID: 20372981
I don't think you need the ticks, just fix the varchr/varchar problem.

$type = 'varchar(255)';
0
 
LVL 3

Assisted Solution

by:BioI
BioI earned 150 total points
ID: 20373014
the problem with your insert-statement is that mix up your parentheses and quotes
copy-past this line:


$result = mysql_query("INSERT INTO profile_fields (`id`, `name`, `profile_id`, `type_id`, `order`) VALUES  ('0', '$name', '$profileid', '$type', '$order')") or die(mysql_error());

Open in new window

0
 
LVL 20

Assisted Solution

by:steelseth12
steelseth12 earned 350 total points
ID: 20373109
You should use backtcks especially when executing alter statements dynamically ...

for example if you use

$name = 'sort'; which is a reserved word it will fail without the backticks.

on this query

$result = mysql_query("INSERT INTO profile_fields (`id`, `name`, `profile_id`, `type_id`, `order`) VALUE
  ('0', '$name', '$profileid', '$type', '$order'") or die(mysql_error());

as BioI suggested above you are missing a closing ) and using value instead of values

Also if your id field is autoincremented PK then you should use

$result = mysql_query("INSERT INTO profile_fields (`name`, `profile_id`, `type_id`, `order`) VALUES
  ('$name', '$profileid', '$type', '$order')") or die(mysql_error());

 
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

Suggested Solutions

Title # Comments Views Activity
Password forgotten. 3 40
MySQL Grouping 2 21
Split long text string into shorter chunks 7 21
Make custom query_posts look show the excerpt only 9 24
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…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to dynamically set the form action using jQuery.
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.

911 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

19 Experts available now in Live!

Get 1:1 Help Now