jakebur01

thank you

So if I set the column up as DATETIME and use now() in my insert query I should be good?

What type of column should the MySQL table be. How should it be set up?

Thank you.

What is the correct way to insert the date of a post into a mysql table and how should the mysql column be set up? I read somewhere that the correct format for xml / rss should be like Wed, 04 Feb 2008 13:00:00 GMT .

I learn several new things every day in php. It is one of the only things I am halfway good at. Thank you so much! That finalized the script I have been working on all week.

The table was created based on the number of columns the text file had in it and they were generated based on that flat file. ie. col1, col2, col3, col4, etc. On page 2 I select which columns contain which like list, dealer, distributor and so on. INSERT INTO {$tablename} VALUES $part = $itemnum, $list = '{$row[$list]}', $dealer = '{$row[$dealer]}', $distributor = '{$row[$distributor]}', $sort = '{$row[$sort]}', $stdpack = '{$row[$stdpack]}' I am wanting to insert the list, dealer, distributor, sort, and stdpack values into the appropriate columns. As you can see, I have those columns stored in variables ($part, $list, $dealer, $sort, & $stdpack). How can I get this done and still take care of the columns I don't know about that are in the table?

The table was created based on the number of columns the text file had in it and they were generated based on that flat file. ie. col1, col2, col3, col4, etc. On page 2 I select which columns contain which like list, dealer, distributor and so on. So in this line: INSERT INTO {$tablename} VALUES $part = $itemnum, $list = '{$row[$list]}', $dealer = '{$row[$dealer]}', $distributor = '{$row[$distributor]}', $sort = '{$row[$sort]}', $stdpack = '{$row[$stdpack]}' I am wanting to insert the list, dealer, distributor, sort, and stdpack values into the appropriate columns. How can I get this done and still take care of the columns I don't know about that are in the table?

Yea, but I don't know what those other fields are. There could be 20 other columns. But I am trying insert into those few and put NULL into whatever else is out there.

How can I insert data only into specific columns and have it null whatever other fields are on the row? Please see the else statement below which is incorrect but I am working on it. <?php $sql = "SELECT * FROM {$tablename} WHERE ($part LIKE '%-1' OR $part LIKE '%-2' OR $part LIKE '%-3' OR $part LIKE '%-0')"; $result = mysql_query($sql) OR DIE(mysql_error()); while ($row = mysql_fetch_assoc($result)) { $itemNum = substr($row[$part], 0, -2); $sql2 = "SELECT * FROM {$tablename} WHERE $part = $itemNum"; $result2 = mysql_query($sql2) OR DIE(mysql_error()); $num_rows = mysql_num_rows($result2); if($num_rows>0) { $update = "UPDATE {$tablename} SET $list = '{$row[$list]}', $dealer = '{$row[$dealer]}', $distributor = '{$row[$distributor]}', $sort = '{$row[$sort]}', $stdpack = '{$row[$stdpack]}' WHERE $part LIKE '{$itemNum}%'"; mysql_query($update) or die(mysql_error()); } else { $insert = "INSERT INTO {$tablename} VALUES $part = $itemnum, $list = '{$row[$list]}', $dealer = '{$row[$dealer]}', $distributor = '{$row[$distributor]}', $sort = '{$row[$sort]}', $stdpack = '{$row[$stdpack]}'"; mysql_query($update) or die(mysql_error()); } } ?>

I know the else is incorrect. How can I do it correctly and insert values into the columns specified and NULL or 0 into the remaining columns? <?php $sql = "SELECT * FROM {$tablename} WHERE ($part LIKE '%-1' OR $part LIKE '%-2' OR $part LIKE '%-3' OR $part LIKE '%-0')"; $result = mysql_query($sql) OR DIE(mysql_error()); while ($row = mysql_fetch_assoc($result)) { $itemNum = substr($row[$part], 0, -2); $sql2 = "SELECT * FROM {$tablename} WHERE $part = $itemNum"; $result2 = mysql_query($sql2) OR DIE(mysql_error()); $num_rows = mysql_num_rows($result2); if($num_rows>0) { $update = "UPDATE {$tablename} SET $list = '{$row[$list]}', $dealer = '{$row[$dealer]}', $distributor = '{$row[$distributor]}', $sort = '{$row[$sort]}', $stdpack = '{$row[$stdpack]}' WHERE $part LIKE '{$itemNum}%'"; mysql_query($update) or die(mysql_error()); } else { $insert = "INSERT INTO {$tablename} VALUES $part = $itemnum, $list = '{$row[$list]}', $dealer = '{$row[$dealer]}', $distributor = '{$row[$distributor]}', $sort = '{$row[$sort]}', $stdpack = '{$row[$stdpack]}'"; mysql_query($update) or die(mysql_error()); } } ?>

My first problem is fixed but not my second.

What about this? while ($row = mysql_fetch_assoc($result)) { $itemNum = substr($row[$part], 0, -2); $update = "UPDATE {$tablename} SET $list = '{$row[$list]}', $dealer = '{$row[$dealer]}', $distributor = '{$row[$distributor]}', $sort = '{$row[$sort]}', $stdpack = '{$row[$stdpack]}' WHERE $part LIKE '{$itemNum}%'"; mysql_query($update) or die(mysql_error()); } Is there a way I can add the item to the database if it doesn't find it doing an update. Also, I am unsure of how many columns are in the table. It is different every time. But, the only columns I need are the ones specified in the query above.

I have two problems. The first is that if there is not a " WHERE $part LIKE '{$itemNum}% " then I need to add one to the table. The second problem I am having is I need a better way to clean up my rows than to use array('', '$-', '0', '0.0', '0.00', '$ 0', '$ -', '$0.0', '$0.00', '-', ' ', '$', '$- ', '$- '). I would like it if I could delete the rows where it does not have a numeric value above .01 . while ($row = mysql_fetch_assoc($result)) { $itemNum = substr($row[$part], 0, -2); $update = "UPDATE {$tablename} SET $list = '{$row[$list]}', $dealer = '{$row[$dealer]}', $distributor = '{$row[$distributor]}', $sort = '{$row[$sort]}', $stdpack = '{$row[$stdpack]}' WHERE $part LIKE '{$itemNum}%'"; mysql_query($update) or die(mysql_error()); } $inArray = array('', '$-', '0', '0.0', '0.00', '$ 0', '$ -', '$0.0', '$0.00', '-', ' ', '$', '$- ', '$- '); $checkData = implode("', '", $inArray); mysql_query("DELETE FROM {$tablename} WHERE $dealer IN({$checkData}) OR $distributor IN({$checkData}) OR $list IN({$checkData})");

ha ha... it echoed the $sql out 7,000 times. Thank you. It fixed it. When setting $_SESSION and stuff like that, do you use single quotes there too? Ex. $_SESSION['sample'];

I have looked for case sensitivity, added quotes, and removed quotes and I cannot make this error go away. PHP Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in else { $result2 = mysql_query("SELECT * FROM smith_prog_item WHERE item = $partnumber LIMIT 1"); while ($row2 = mysql_fetch_assoc($result2)) { if($row2["type"]=="NGK") { $c17="100"; $c18="$level6";} elseif ($row2["type"]=="OTHER") { $c11="$level6"; $c12="$level6"; $c13="$level6"; $c14="$level6"; $c15="$level6"; $c16="$level6"; } } } Also, this is within another while loop.

Thank you.

thank you.

How can I make the below a 1 if it is not a number or if it is nothing at all. This column contains values like 3, 50, RL, , 2, 1, etc. I want to keep all the current numbers, but make it a 1 if it is blank or if it contains letters. $stdpack1=$row["$stdpack"];

I am updating records in the same table. I have part numbers ending with -1, -2, -3, -0 of which whose values need to be copied to the rows that do not contain that ending. Example: 35-184-3 's values would be copied to 35-184 if it exists.

It is throwing me this error. 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 'SET col8 = ' $11.04 ', col6 = ' $5.52 ', col5 = ' $3.87 ', col1 = '419', col9 = ' at line 1

Would it be better to do something like this? //suggested by premiso $sql = "SELECT * FROM {$tablename} WHERE ($part LIKE '%-1' OR $part LIKE '%-2' OR $part LIKE '%-3' OR $part LIKE '%-0')"; $result = mysql_query($sql) OR DIE(mysql_error()); while ($row = mysql_fetch_assoc($result)) { $itemNum = substr($row[$part], 0, -2); $update = "UPDATE SET $list = '{$row[$list]}', $dealer = '{$row[$dealer]}', $distributor = '{$row[$distributor]}', $sort = '{$row[$sort]}', $stdpack = '{$row[$stdpack]}' WHERE itemNumber LIKE '{$itemNum}%'"; mysql_query($update) or die(mysql_error()); }

This query boggs down my server and I cannot figure out why. here is the query mysql_query("UPDATE {$tablename} AS a, {$tablename} AS b SET a.$list =b.$list, a.$dealer=b.$dealer, a.$distributor=b.$distributor, a.$sort=b.$sort, a.$stdpack=b.$stdpack WHERE LEFT(b.$part, CHAR_LENGTH(b.$part)-2)=a.$part AND RIGHT(b.$part, 2) IN ('-1', '-2', '-3', '-0')") or die(mysql_error()); and this is what it prints UPDATE smith118986 AS a, smith118986 AS b SET a.col8 =b.col8, a.col6=b.col6, a.col5=b.col5, a.col1=b.col1, a.col9=b.col9 WHERE LEFT(b.col2, CHAR_LENGTH(b.col2)-2)=a.col2 AND RIGHT(b.col2, 2) IN ('-1', '-2', '-3', '-0')

bump...

Does anyone see anything wrong in this code? I can't see any errors because the page will not even load due to this little block of code. mysql_query("UPDATE {$tablename} AS a, {$tablename} AS b SET a.$list =b.$list, a.$dealer=b.$dealer, a.$distributor=b.$distributor, a.$sort=b.$sort, a.$stdpack=b.$stdpack WHERE LEFT(b.$part, CHAR_LENGTH(b.$part)-2)=a.$part AND RIGHT(b.$part, 2) IN ('-1', '-2', '-3', '-0')") or die(mysql_error());