Moving data to another table

[c1lonewolf]

I need to move info from several data tables to a temporary single table for redevelopement. I found a script that allows me to upload info to the table via a file. However, the current info is scattered and pulled together like this (which I don't really understand):

$result = mysql_query("select a.Price,a.City,a.Country,a.Title,a.Description,a.Category,a.recdate,a.images,a.image2,a.image3,a.image4,a.imgWidth,a.imgHeight,a.ftype,b.stateName,c.id,c.cateName,a.email from ads_list as a LEFT OUTER JOIN sys_states as b ON b.id=a.State LEFT OUTER JOIN ads_cate as c ON c.id=a.Category where a.id=" . $id . ";") or die(mysql_error());
if (mysql_num_rows($result) <= 0) {
	die("data not found!");
} else {
	$rs = mysql_fetch_array($result);
	//$Title = stripslashes($rs["Title"]);
	$Title = $rs["Title"];
	//$Description = nl2br($rs["Description"]);
	$Description = $rs["Description"];
	$State = $rs["stateName"];
	$City = $rs["City"];
	$Country = $rs["Country"];
	$Types = $rs["ftype"];
	$AdCategory = $rs["Category"];
	$CatID = $rs["id"];
	$Category = $rs["cateName"];
	$images = $rs["images"];
	$image2 = $rs["image2"];
	$image3 = $rs["image3"];
	$image4 = $rs["image4"];
	$imgWidth = $rs["imgWidth"];
	$imgHeight = $rs["imgHeight"];
	$Email = $rs["email"];
	$Price = $rs["Price"];

If I use this and send info to the file will the original use of
htmlspecialchars() cause problems when loading to new table? Is there an easier way?

[Old Pedant]

ummm...yes. Use SQL. Don't use PHP.

One *POSSIBLE* example:

CREATE table_backup LIKE my_table;

INSERT INTO table_backup
SELECT * FROM my_table;

Done.

But there are many many many other possibilities.

DO NOT USE PHP FOR THIS!

(You can use PHP, but use SQL queries to move/copy the data!)

[Old Pedant]

Just for example, looking at the query in your first post,

SELECT a.Price,a.City,a.Country,a.Title,a.Description,a.Category,a.recdate,
      a.images,a.image2,a.image3,a.image4,a.imgWidth,a.imgHeight,
      a.ftype,b.stateName,c.id,c.cateName,a.email 
FROM ads_list as a LEFT OUTER JOIN sys_states as b 
ON b.id=a.State 
LEFT OUTER JOIN ads_cate as c ON c.id=a.Category 
where a.id=" . $id . ";"

I would assume that in this new table you are creating you want *ALL* ads_list.ID values, so you would just omit the WHERE clause.

Tack an an INSERT INTO newtable ( list, of, fields, ... )
and you are done.

*********

Having said all that...

Are you sure it's a good job to JOIN the tables into the single temp table?

Once joined, it would be very hard to get the data back into separate tables.

Maybe it makes more sense to simply back up the three tables separately? If you need them joined during your redevelopment, JOIN them in the same way as that query, but using the backup tables.


if (mysql_num_rows($result) <= 0) {

[c1lonewolf]

Thanks OP for the help...I'm having to restructure the database and redevelope all the coding from the original. Really detailed stuff. Later I intend to separate things into different tables but for now just to get things up an running I have to use a single temp table. Unlike most I'm a newbie to php & mysql so I have a lot of problems.

I did manage to use the 'JOINED' code and add all elements to the new table by extracting them and just inserting into the new one using a simple script...no external file needed.

However, a few that have single quotes (') caused errors. This I don't understand since it came out of the datatable and put right back in the same way.

Which brings me to my next question...could someone help out a newbie by helping me create a function that sanitizes database entries based on what they're used for?

I've seen where some site say to use mysql_real_escape_str() and others say not to, some have it changes because of magic quotes being on/off but I don't know what that is so I would like to build a function based on string usage like 'text-only' striphtmltags(), addslashes() etc. then a reverse if needed for page output.


function sanitze_db_str($purpose, $str){
$tmp = $str;
switch($purpose){
case 'text-only':
$tmp = addslashes($tmp);
$tmp = striptags($tmp);
break;

}
return $tmp;
}

Something along those lines...can anyone help?

[guelphdad]

Again the suggestion is DON'T use PHP at all.

[c1lonewolf]

Well I don't know sql and don't have time to learn it...just yet.

example:addslashes()-vs-mysql_real_escape_string()-the-final-debate

[Old Pedant]

If you won't use SQL, then you will be constantly stuck with crap like you are experiencing now.

And I still say you made a mistake joining those tables to create the backup table. Keeping them separate UNTIL you have your new database design complete is bound to pay dividends in the long run.