Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Sep 2002
    Posts
    462
    Thanks
    0
    Thanked 20 Times in 20 Posts

    Moving data to another table

    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):
    Code:
    $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?
    NO Limits!! DHCreationStation.com
    ------------------------------------------------------------
    For projects using MediaTypes (MIMETypes) visit E-BAM.net -(updated weekly)

    Broken items wanted for tinkerin'! PostItNow@BrokenEquipment.com
    Global Complaint Dept.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    ummm...yes. Use SQL. Don't use PHP.

    One *POSSIBLE* example:

    Code:
    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!)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    Just for example, looking at the query in your first post,

    Code:
    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) {
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Regular Coder
    Join Date
    Sep 2002
    Posts
    462
    Thanks
    0
    Thanked 20 Times in 20 Posts
    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?
    NO Limits!! DHCreationStation.com
    ------------------------------------------------------------
    For projects using MediaTypes (MIMETypes) visit E-BAM.net -(updated weekly)

    Broken items wanted for tinkerin'! PostItNow@BrokenEquipment.com
    Global Complaint Dept.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Again the suggestion is DON'T use PHP at all.

  • #6
    Regular Coder
    Join Date
    Sep 2002
    Posts
    462
    Thanks
    0
    Thanked 20 Times in 20 Posts
    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
    NO Limits!! DHCreationStation.com
    ------------------------------------------------------------
    For projects using MediaTypes (MIMETypes) visit E-BAM.net -(updated weekly)

    Broken items wanted for tinkerin'! PostItNow@BrokenEquipment.com
    Global Complaint Dept.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •