PHP :: Exclude A Column From A Mysql Command

Displaying MySQL Column Names Using PHP
I'd like to be able to display the column names of a table on a page. Is there a way to read that information in using SHOW? And then to display that information again? Or would I have to store these names in a seperate table and display these values?

I tried this, but it sure didn't work:
$result = mysql_query ("show columns from industries");

print ("<table>");
if ($row = mysql_fetch_array($result)) {
do {
print ("<tr><td bgcolor=#cccccc>");
print $row["field"];
print ("</td><td bgcolor=#cccc00>");
print $row["type"];
print ("</td><td bgcolor=#cccc00>");
print $row["null"];
print ("</tr>"); }
while($row = mysql_fetch_array($result)); }
else {print "Sorry, no records were found!";}

print ("</table>");

Check If Column In Mysql Exists With Php
I wanted to know if you can let php check if a column exists in a table.
cuz i have a 1 script script and i want it to check a table for having the following columns and if 1 o fthem is missing it would automaticly add the column.

Get Mysql Unique Column Fields
I have data with the field name "monthyear" and I am trying to get a list of all the different field names without repeating like "may2003, january2001, june2002", this is for my news script i am making, im going to use it to make an archives page which is organized by month any ideas?

Should I Add A Multi Column Index To MySQL?
I have a mysql table. Two columns in the table are used to pull out
stories from the table.

One of the columns stands for categories of the stories, and the other
column is the primary key and has the id for each story.

I am thinking of writing queries like this:

mysql_query("SELECT * FROM articles WHERE category='Science_Fiction'
AND story_id=ཀྵ'");

I am wondering if I should create a multi column index that covers both
"categories" and "story_id" columns. -- INDEX (categories, story_id)

Would this increase performance?

Numeric Column Names In Mysql?
i am trying to create a table with a column name called 2004. But I
keep getting a mysql error? Is there any workaround?

Unable to execute: 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 &#55614;&#57156; TEXT,2004_reference TEXT,2005
TEXT,2005_reference TEXT,200

CREATE TABLE test (id int(6) NOT NULL auto_increment, membership_number
TEXT,first_name TEXT,last_name TEXT,spouse TEXT,phone_priv
TEXT,phone_bus TEXT,address_street TEXT,addr_town TEXT,addr_city
TEXT,membership TEXT,donor TEXT,1998sa TEXT,1999sa TEXT,2000sa
TEXT,200s1 TEXT,2002sa TEXT,2003sa TEXT,2003_reference TEXT,2004
TEXT,2004_reference TEXT,2005 TEXT,2005_reference TEXT,2006
TEXT,2006_reference TEXT, PRIMARY KEY (id),UNIQUE id (id))

Question About Datetime Column In Mysql
i tried this question in the mysql group, but have not heard anything.
i have a database with about 20 tables. The some of these tables have
datetime type columns in them. What i am basically wondering is if
there is a way to make a default value of now() apply if it is not
specified when the row is created.
i know this is possible with a timestamp, but is it possible with a
datetime column ? and if so, how ?

MySQL, Turn Column Into Array
Today is the first time I've used MySQL with PHP.

What is the best way to turn a column into an array?

This is how I am doing it at the moment, surely this is sub-optimal:

$query = 'SELECT something FROM table'
$result = mysql_query( $query );
$i = 0;
while( $arrayTemp = mysql_fetch_array( $result ) )
{
$array[ $i++ ] = $arrayTemp[ 0 ];
}
echo "<ul>";
foreach( $array as $string )
{
echo "<li>$string</li>";
}
echo "</ul>";

Showing Everything In A Column In A Mysql Table
Need to know how to echo an entire column in a MySQL table. I've tried various things but they only echo the one at the top.

Mysql Php Display Column In Dropdown?
is it possible to show a column from mysql in a dropdown and then when that row is selected to display the information in an editable form ??

Exclude Keywords From RSS Search
I have a script which parses the BBC RSS feed once an hour, and drops
any stories with certain keywords into a database. I would like to ban
certain strings; at the moment the script will pick up the word "train"
but also "training"; this is giving me a lot of false positives. Can
anyone assist? I tried a couple of things but nothing that works.

Script is as follows:

<?php

$keywords = array("keyword1", "keyword2", "keyword3");
$bannedwords = array("bannedword1", "bannedword2", "bannedword3");
$feedsource =
"http://news.bbc.co.uk/rss/newsonline_uk_edition/uk/rss091.xml";

$db = mysql_connect("localhost","username","password") or
die(mysql_error());
mysql_select_db("database");

$insideitem = FALSE;
$tag = "";
$title = "";
$description = "";
$textdump = "";
$link = "";
$itemcount = FALSE;
$body1 = "";

function startElement($parser, $name, $attrs) {
global $insideitem, $tag, $title, $description, $link;
if ($insideitem) {
$tag = $name;
} elseif ($name == "ITEM") {
$insideitem = TRUE;
}
}

function endElement($parser, $name) {
global $insideitem, $tag, $title, $description, $link, $keywords;
$numkeywords = count($keywords);
$duplicate = FALSE;

if ($name == "ITEM") {
for($counter1=0; $counter1 < $numkeywords; $counter1++) {
if(stristr($title, $keywords[$counter1]) || strstr($description,
$keywords[$counter1])) {
$sql = "select * from tblRSSfeed";
$result = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
if($row[txtLink] == trim($link)) {
$duplicate = TRUE;
}
}
if($duplicate == FALSE) {
$itemcount = TRUE;
$datetime = date("Y-m-d H:i:s");
$title = trim(str_replace("'", "'", $title));
$description = trim(str_replace("'", "'", $description));
$link = trim($link);
$sql = "INSERT INTO tblRSSfeed VALUES(NULL, '$title',
'$description', '$link', '$datetime')";
mysql_query($sql) or die(mysql_error());
} else {
$duplicate = FALSE;
}
}
}
$title = "";
$description = "";
$link = "";
$insideitem = FALSE;
}
}

function characterData($parser, $data) {
global $insideitem, $tag, $title, $description, $link;
if ($insideitem) {
switch ($tag) {
case "TITLE":
$title .= $data;
break;
case "DESCRIPTION":
$description .= $data;

break;
case "LINK":
$link .= $data;
break;
}
}
}

$xml_parser = xml_parser_create();
xml_set_element_handler($xml_parser, "startElement", "endElement");
xml_set_character_data_handler($xml_parser, "characterData");
$fp = fopen("$feedsource","r") or die("Error reading RSS data.");
while ($data = fread($fp, 4096)) {
xml_parse($xml_parser, $data, feof($fp)) or die(sprintf("XML error: %s
at line %d", xml_error_string(xml_get_error_code($xml_parser)),
xml_get_current_line_number($xml_parser)));
}
fclose($fp);
xml_parser_free($xml_parser);

?>

Searching A Mysql Column Against Multiple Variables
I have an array of zipcodes which I am trying to check against a mysql table. I want to search in the column "zipcode" in a table of a bunch of entries. I want to search each rown to see if the value of the zipcode column is listed within my array.

This could be said vis vera, that I want to check the mysql table to see if any of the rows have one of the values from the array inside the zipcode column.

I've tried the statement

"select * from table where zipcode IN ('$zipsearch')", but it seems to return an odd sequence of data. It doesn't seem thurow enough.

similarly the entire statement is :
select * from table where description like "%$trimmed%" OR title like "%$trimmed%" AND zipcode IN ('$zipsearch') order by date"

I can't seem to get the statemtent to break up my requests right so that the logistics of teh statement are :

select from table where (descriptions like variable or title like variable) AND <zipcode search problem>

How To Display Newly Added Mysql Column
I have a mysql table and i want the user to add columns using a php
interface and then display the table with the newly added column. I
was thinking about putting all the columns names in a special table
but there must be another more efficient way.

as an example this is my query to display the table:
$result = mysql_query("select * from news");
while($r=mysql_fetch_array($result))
{
$title=$r["title"];
$message=$r["message"];
//display the row
echo "$title $message ";
}

now if the user alter the table and add a column "rank", how can i
update my query automatically so it can display the newly added row
(or column)?

MySQL Query Returning Empty Column
Here's my code:

$query_get_machine = sprintf("SELECT machines.ID, machines.NotifyID, machines.Name, notify.ID, notify.`Data` FROM machines RIGHT JOIN notify ON machines.notifyID = notify.ID WHERE machines.ID = %s", GetSQLValueString($colname_get_machine, "int"));

Can anyone tell why the notify.`Data` column might be returned empty? All others display as they should.

If Result Of Mysql Query Is Only One Row/column, Why Use An Array?
If I'm doing a very specific select statement, which I know will only ever return one value, can I get that resulting bit of data without storing it in an array or object?
If not, is mysql_fetch_array() the fastest method for getting this one value?

Select Single Column From Mysql Into Array
Is there a way to select a single column from a mysql database and
directly put the results into an arrray? Here is the workaround below,
but I would like to elimniate the array_push step:

$uids=array();
$res = mysql_db_query("db", 'select uid from tbl;', $link);
while ($row = mysql_fetch_row($res)) {
array_push ($uids, $row[0]);

}

MySql VCHAR Column Converted To Float By PHP :-(
I fetch the following value from a string (VCAHR(250))colmun in a MySql
table:
"30000000000000000000000000000000000000000000000000 0"

When I write it out using echo() I get :
3E+50

Appearently the PHP interpreter converts the VCAHR value automatically
in a float despite the fact that the value is defined as a VCAHR in
MySql.
While trying to convert float to string back in PHP using type casting
or settype() I get always the string "3E+50".

Does someone know how one can fetch the VCHAR as string?
Does someone know how to print a formatted float to get the desired
result? I tried printf() but no success.

MySql Error 'Unknown Column' From PHP Variable
I'm getting an error message that makes no sense to me. I have a table with a
field named 'testkey' for a list of "keys" in the form of ABC10102. I have a
php variable holding a specific key number.

The relevant code is -
$_testkey = 'LAL10102'
$sql = "SELECT * FROM `pro_keys` WHERE `keynum`=`$_testkey`";
$result=mysql_query($sql,$db_conn);

echoing the $sql gives -
SELECT * FROM `pro_keys` WHERE `keynum`=`LAL10102`

But I get the following error -

MySQL error #1054 - Unknown column 'LAL10102' in 'where clause'

?

Number Of Identical Entries In MySQL DB Column
How do you display the number of times a particular entry appears in a column of a database?

Regular Expression, Exclude Commas
Greetings all. I'm a newbie, desperate to learn, and I've searched here and Google for such things as "PHP regular expression comma" and variations on that theme, but I haven't been able to figure out what I'm doing wrong, though I've visted several sites and tutorials dealing with regular expressions.

I've got a form on my bosses' Web site where a customer can enter a price range and get a list of our "stuff" that falls within that price range. I'd like to set something up so that if they enter letters or commas or anything other than numerals in the price range form fields, they'll be gently encouraged to go back and do it right. :)

This is what I've got for the time being:

if (!eregi('^[0-9]', $pricemin) ¦¦!eregi('^[0-9]', $pricemax)) {
echo 'You have not entered a valid price range. '
echo 'Be sure to enter only numbers, and no letters or punctuation marks, when searching for a price range. '
echo 'Click the Back button on your browser to search again.'
exit;
}

This works, insofar as if someone types "blah" in the pricemin or the pricemax form field, they'll get the message. But if some well meaning person types "50,000" or "$50,000" instead of "50000" in there, they currently get a MySQL error. I've got a note telling people not to use letters or punctuation, but you know how people are.

All the tutorials I've seen talk about including or excluding different characters based on different criteria, but everything I've tried to put a comma in there has failed and/or messed up the query somehow. What am I missing?

Preg_match Exclude String Within Subpattern
I have a simple pattern that captures an anchor tag:

(<a.*?</a>)

It works great. I would now like to modify this to EXCLUDE occasional
opening and closing bold tags () around the text.

For example:

<a href="http://www.example.com>Example</a>

to return

<a href="http://www.example.com>Example</a>

How can this be accomplished?

How To Exclude A Specific Email Address
I wish to exclude a specific email address when one sends an ecard from my site. How does one exlude this email address but allow all others to continue on their merry way?
Code currently used:

function validEmail($address)
{
if (eregi("^[a-z0-9]([._-]?[a-z0-9])*@[a-z0-9]([.-]?[a-z0-9])*.[a-z]{2,}$", $address))
return true;
else
return false;}

Valid MySQL Database/table/column Name Regexp
regular expression in PHP which could be used to
check that a proposed (My)SQL database/table/column name is valid, i.e.
shouldn't result in an SQL error when created?

The user of my (hopefully to be opensourced) program has the ability to
create database/table/column names on the fly.

I'm aware of obvious characters such as ., [space], things like >, etc.,
which won't work, but haven't been able to source a definitive list,
including having googled the MySQL site.

Obviously certain characters need to be filtered out, as noted above, but
I want to be as unrestrictive as possible; hence just [a-z]* isn't good
enough because things like _ are acceptable.

Ideally, I'd prefer a regexp that applies to _all_ vendors' databases, not
just MySQL as I'm about to migrate the program to being
database-independent, probably using PEAR DB, but even a MySQL-specific
regexp would do the job.

In the longer term, I plan some sort of entity conversion script so that
theoretically any character could be used, using some sort of escaping
mechanism probably.

Column Data-Type For Storing Serialized Array In MySQL?
I'm really not sure - I used BLOB in the past- but the arrays are only a few kilobytes. But VARHCAR only allows 255 characters at most and the arrays might contain many more characters than that.

MySQL Query - INSERT - Unknown Column 'test' In 'where Clause'
I have a registration script on my website, and it was working fine, I added a second MySQL INSERT query to it, for another table so I can use JOIN's later on with the second table, but I keep getting the same error:

Unknown column 'test' in 'where clause' can someone see whats wrong with this? Code:

Mysql Clear Row Command?
is there a mysql function that will just zero out (but not delete) all column values for a given row? so the desired query would look something like this:

update table
set
*=''
where id=$id

MySQL UPDATE Command
I use this to add a new row of data to a table:

<BLOCKQUOTE>code:<HR><pre>$query = "insert into $parent values('$id','$author','$website','$fname','$width','$place','$title')";
$result = mysql_query($query) or die("crap");[/code]

The $id is an auto incrementing field so every row can be referenced with a unique number. Suppose I have the $id of the row I want to modify, and I have new values for $author, $website, $fname, $width, $place, and $title. What code do I use to modify the row (keeping the same $id)?

Select Count But Exclude Counting Empty Fields?
Hi, any pointers on how I can exclude counting any instances where a field is empty? i.e I have

$sql = 'SELECT count(reviewid) as num_times FROM reviews WHERE reviewid =' . $id;

I use this to get an average. But some times the average field is empty. So in the above I want to exclude it counting where the average is blank.

Get Rows In Mysql At Command Prompt
This question really belongs more in a mySQL group, but my NNTP server
doesn't have a mySQL group available.

I'm trying to get number of rows in a table without doing a "select * from
table" or even a "select id from table". I know in PHP you put:
mysql_numrows() but what's the equivelant in command prompt?
I searched mysql.net and can't find anything.

Mysql Command Line Client
Is there any way of making the mysql command line client full-screen?
Sometimes it's easier to use the client than go thru php, but
since it's only about 80 characters wide that limits its usefulness.

Using PHP For Mysql Command (update Partial)
I have a table with a structure column of "product_attributes" that I want to do a mass update to with certain criteria. Basically if within product_attributes there are some rows with the following: Code:

color_blue,color_pink,color_purple,color_black
color_blue,color_pink,color_purple,color_white
color_green,color_pink,color_purple,color_black
color_blue,color_pink,color_purple,color_yellow

Now I want to change all instances of color_purple to color_violet. So that it should now read: Code:

color_blue,color_pink,color_violet,color_black
color_blue,color_pink,color_violet,color_white
color_green,color_pink,color_violet,color_black
color_blue,color_pink,color_violet,color_yellow

How would I do this without disturbing any of the other information within that table? Only the one word in all rows with "color_purple" in it?

MySQL Database From The Command Line
I am creating a MySQL database from the command line for the first
time. I am creating a database called 'goldminers' with 1 table called
'goldminers'. What am I doing wrong here:

mysql> use goldminers
Database changed
mysql> drop table goldminers
-> ;
Query OK, 0 rows affected (0.13 sec)

mysql> make table goldminers
-> (id int unsigned not null primary key,
-> name char(30) not null,
-> dateIssued date not null,
-> location char(20) not null,
-> comment text null,
-> url text null);
ERROR 1064: You have an error in your SQL syntax near 'make table
goldminers
(id int unsigned not null primary key,
name char(30) not n' at line 1

The reason that I didn't make the ID auto_increment is that, even
though it is generally incrementing by 1 every time, there are some
places where the database skips numbers because of goldminers who we
don't have any information on. I need my database's IDs to match the
IDs on the existing paper-version of the database.

HOW: To Exclude Html Special Chars From Encrypted Url Variable String
I am passing the username and password encrypted in my url strings...

I have discovered that html special characters should be excluded from
the string...specifically the ">" character which when included will
display the rest of the encrypted characters as regular printable
characters on the web page.

Favor 1:
Does anyone know which html chars should be excluded?

Favor 2:
Is there a faster way to check if a character already
exists in a string besides substr()?...(this is for looking for a
character substitute)

Finally : Is there another way to pass parameters opaquely
through an href link?

How To Query From One Column Then Sort Using Another Column
I am trying to get the last 100 albums I entered into my database using the album_id (Highest being the latest entry) then sort them alphabetically using the album_title column.

I can get the latest 100 easily but cannot figure out how to also sort the results by the album_title before I display them.

This is what I have so far which gets what I want but not in alphabetical order.

<?php
$sqlquery = mysql_query("SELECT * FROM albums ORDER BY album_id desc limit 100", $db);

while ($result = mysql_fetch_row($sqlquery))
{
echo "<a href='music_info.php?album_id=$result[0]'>$result[1] </a>";
}
?>

I have been trying to add a second ORDER BY to the query like so.

$sqlquery = mysql_query("SELECT * FROM albums ORDER BY album_id desc limit 100 ORDER BY album_title", $db);

but this obviously isn't the way I should be doing it.

Paging And Sorting Column By Column Name?
I am using below code to paging my data. I want to sort my data by Column time. Not getting where i do changes. Code:

Using MySQL Command Load Data Infile
When I execute this SQL statement in my PHP code, I get an error
"File '.Address.txt' not found (Errcode: 2)"

$File = addslashes(".Address.txt");
$SQL = "Load Data InFile "" . $File . "" into table addresses";
$result = mysql_query($SQL) or die(mysql_error());

The file is located in the same directory as my .PHP file.

How do I generate a relative address for this file so that it can be found?
I'm running on XP Prof..

Execute MySQL Command After Viewed The Page For 20 Seconds
Is it possible to somehow wait for a user to stay on the site for 20 seconds and after that issue a MySQL command? I'm creating a system that gives registered users points for viewing pages etc.

I thought it might be done with Javascript, but a quick glance to the Javascript DevTalk teached me that it is not possible, because Javascript works on the client.

UPDATE Command Is Not Working On MySQL, Windows 2000 Server
Hi all,

I encountered mysterious problem with MySQL and PHP.

UPDATE command is not working on MySQL configured on Apache 2 with Windows 2000 Server.

It pulled my hairs for atleast 5 hours.

MySQL says Affected rows: 0 (Query took ~ sec) always?

Now, I've decided to move all PHP files and db to remote server.

Can anyone suggest possible reasons for above errors?

MySql Not Enable From IIS But Worksfrom Command Line -- With Partial Solution
Problem: MySQL library bound ONLY when PHP is run from the
command prompt, and NOT when PHP is run from an IIS Web page.
(Error indicates that mysql_connect is an unknown function.)
Not only does the library bind from the command prompt but data is
retrieved using the EXACT same program file that fails under IIS....

Mysql Command "for Each Entry"?
I've got my database with the ID field being a auto-incrementing index. Now, I also have a field myID, which I want to keep as a identifying number of each record, from 1 to whatever - without the gaps that exist in ID from records being deleted. I now have it set up to delete the column and re-create it, but I know this is not efficient. Is there any other way to do this? My only other thought is to use code similar to this:

function resetMyID($table) {
$result = mysql_query("SELECT * FROM $table", $link);
$count = 0;
??? for each successive entry {
??? $query2 = "UPDATE $table SET myID = $count WHERE ???";
mysql_query($query2);
}}

I can't say where id= whatever, because the id has holes in it ... any ideas?

Two Column Display.
I'm trying to display records in two columns. Here's my logic so far...

$numRows=get the total number of rows
$x=divide total rows in half (rounding up for odd #'s)

do a fetch_array on the query

I store the columns into two table cells, I start the main table here Start first table cell. First column of data=do a for loop from $i=1 to $x break into HTML and start the content. End First table cell

Start Second table cell. Second Column of data = do a for loop from $j=1 to $numRows-$x break into HTML and do content. End Second table cell

If anyone sees something inherently flawed with this or just knows a better way, hints and examples are always appreciated. I am an admitted newbie and very used to ASP, so I I think the problem comes from not being able to advance to another record manually. But then again, I really don't know what I'm talking about.

Adding Column
Let's say that I extract some data results from the database and I want to display it such as the following:

ColumnA ColumnB
---------------------------
Result X| Result A |
---------------------------
Result Y| Result B |
---------------------------
| Result C |
---------------------------
Result Z | Result D|
---------------------------

All results come from the same row , just dispatch in several columns.
If I have a variable saying that each result value is ' 1 '.

How do I add column A and display a total (in this case 3)?
How do I add column B and display a total (in this case 4)?

I have to precise that I don't know in advance how many results I 'll collect from the database. The ideal is to post total amount in each column.