PHP - Search For String In Column In Mysql Table Separated By A Character

I have a mysql table named songs with 3 columns, id, artist,title.

Most of the songs in the artist column are correct, ex:

artist
------------------
John Denver
Loretta Lynn
Shania Twain
Luke Bryan

But some of the songs in the artist column are reveresed with a comma, ex:

artist
-----------------
Dever, John
Lynn. Loretta
Twain, Shania
Bryan, Luke

Is there an easy php code snippet or mysql statement that i can use to reverse the order of first name and last name and remove the comma in the last example so the artst columd matches the first example?

I hope this makes sense, thanks, Dale.

I have a table of restaurant menu items called menu_items.  This table has a float row(3,2) called price.  I have a query that looks like this:

$query = mysql_query("SELECT * FROM menu_items WHERE restaurant = '".$restaurantid."'");

How would I find the average of the price row from that query?  Basically I want to find the average item price for a restaurants menu.

Hi guys I hope I am in the right place on this forum, if not I apologise.

A have been working with CS-Cart and had so much trouble trying to update product combinations so I searched for help on the data feeds I am using and found that there was a script that would update my products for os-commerce, this has worked out even worse.

All I really need is for a script to update one column called amount in a Mysql table by referring to the product_code column, Eg If product_code NS324 is= to 5
ad 5 to the amount column.

This sounds simple but I think it might be complex.

Here is the Table

Generated b\: phpM\Admin 3.4.9 / M\SQL 5.5.20-log
SQL quer\: SELECT * FROM `cscart_product_options_inventor\` LIMIT 0, 30 ;
Rows: 30


Column1-----------Column2---------------Column3--------------Column4----------Column5-------Column6-------Column7
product_id-----product_code---------combination_hash-----combination-------amount----------temp-----------position
--29813--------------NS3455----------- --1447985068-----------738_3059-----------0-----------------N-------------------0

So if I can update the amount column using the product_code would solve all of my problems.

So this is what I need to do, I need to add an amount to the amount column related to the product_code NS3455 or update amount or even replace amount it don't matter.

Due to the nature of the feeds I receive I cannot use product_id or combination_hash or combination.

Please help me do this. Lets see who is the clever one.

Thanks for you time

Hi all, I have a table...

id   |   Name       |   Downloads   |
01  |   tom          |    5
02  |   thomas     |    2
03  |   tommy      |    15

I need a button in my page whcih on submit would resent the 'downloads' column so all values are set to 0, Is this possible and how if it is?

Thanks for any advice

Hey Everyone,
I'm creating a site that will show images uploaded for certain days working on a job site. Kind of a day-to-day photo journal for the customer. On the site, the user gets here, sees 3 large images, and a series of thumbnails if more than 3 images exist for that day (works fine).
However, underneath that I want to display a 3-4 column setup of "Archived Dates" that provide a link to the images of the other dates. I have this working correctly, but the results are displayed as follows:

Date 1: Date 2: Date 3:

etc....

I want them to display like this;
Day 1 Day 4
Day 2 Day 5
Day 3 Day 6

and so on..... in a 3 column format. Here is the code I have right now just looping through to display these link results, not the rest of the page. I am trying to do it tableless right now, but if that isn't the right way to go, please let me know.
Thanks to anyone in advance,
Nick

 $SQLRowe = "SELECT DISTINCT RoweImgDate from tblRowe WHERE RoweImgDate !='" . $_GET['date'] . "' Order by RoweImgDate DESC Limit 0, 30";
        //echo $SQLRowe;
        $rsSQLRowe = mysql_query($SQLRowe);

<span class="rowe">Archived Photos:</span><br/>
                    <div id="archive">
                    <?php
                    
                        while($row = mysql_fetch_array($rsSQLRowe)){

                            //echo "<a href='index.php?id="' . $row[RoweImgID] . '"' class='link'>$row[RoweImgDate]</a></br>";
                                    echo "<div id='archivedates'>";
                                    echo "<a href='index.php?date=" . $row[RoweImgDate] . "' class='link'>$row[RoweImgDate]</a>";
                                    echo "</div>";
                                    //echo "<img src='images/$row[RoweImage]'/><br/>";
                                    //echo "<span class='FeatDesc'><p>$row[RoweImgDesc]</p></span><br/>";
                                }
                    ?>
                    </div>

Hey guys, I'm a little confused to how I can do this.   I am basically wanting to give my first column a 'NOT NULL AUTO_INCREMENT' and give each row it's own 'id'. The issue I am having is that the script I am using truncates the whole SQL table with a CSV file that is cron'd daily to update data.   I am currently using this script:

<?php


$databasehost = "localhost";
$databasename = "";
$databasetable = "";
$databaseusername="";
$databasepassword = "";
$fieldseparator = ",";
$lineseparator = "\n";
$enclosedbyquote = '"';
$csvfile = "db-core/feed/csv/csv.csv";

if(!file_exists($csvfile)) {
die("File not found. Make sure you specified the correct path.");
}

try {
$pdo = new PDO("mysql:host=$databasehost;dbname=$databasename",
$databaseusername, $databasepassword,
array(
PDO::MYSQL_ATTR_LOCAL_INFILE => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
)
);
} catch (PDOException $e) {
die("database connection failed: ".$e->getMessage());
}

$pdo->exec("TRUNCATE TABLE `$databasetable`");

$affectedRows = $pdo->exec("
LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." REPLACE INTO TABLE `$databasetable`
FIELDS OPTIONALLY ENCLOSED BY ".$pdo->quote($enclosedbyquote)."
TERMINATED BY ".$pdo->quote($fieldseparator)." 
LINES TERMINATED BY ".$pdo->quote($lineseparator)." 
IGNORE 1 LINES");

echo "Loaded a total of $affectedRows records from this csv file.\n";

?>

Is it possible to amend this script to ignore my first column and truncate all of the data in the table apart from the first column?   I could then give all of the rows in the first column their own ID's any idea how I could do this?   I am still very nooby so please go easy on me

Hey all

I'm working on an auction website. I've added a new column to the 'Auctions' table, however the 'sell.php' page now does not work with this new column (I've deleted the column and tested again and it works, so that's definitely the cause). The new column can not be in a separate table as it needs to pull 'id' values from the 'Auctions' table for various functions.

My question to you is, is there any PHP snippet I can use to tell sell.php to stop reading the 'Auctions' table once it reaches the column I've added? The new column is called 'shipped' if that's of any relevance.

Below is some of  the PHP I'm working on on the sell.php page.


  #//Populate arrays
                $UPLOADED_PICTURES[] = $file;
                $UPLOADED_PICTURES_SIZE[] = filesize($image_upload_path.session_id()."/".$file);
              }
            }
          }
          $_SESSION["UPLOADED_PICTURES"] = $UPLOADED_PICTURES;
          $_SESSION["UPLOADED_PICTURES_SIZE"] = $UPLOADED_PICTURES_SIZE;
          $_SESSION["GALLERY_UPDATED"]=true;
          if($sessionVars['SELL_action']=='edit') {
            $sessionVars["OLD_GALLERYFEE"] = $SETTINGS["picturesgalleryvalue"] * count($UPLOADED_PICTURES);
          } else {
            $sessionVars["OLD_GALLERYFEE"] = 0;
          }
        }
      }
    }
    $with_reserve = $sessionVars["SELL_with_reserve"];
    $reserve_price = $sessionVars["SELL_reserve_price"];
    $minimum_bid = $sessionVars["SELL_minimum_bid"];
    
    $duration_hours = $sessionVars["SELL_duration_hours"];
    $duration_minutes = $sessionVars["SELL_duration_minutes"];
    
    $pict_url=$sessionVars["SELL_pict_url"];
    $imgtype = $sessionVars["SELL_file_uploaded"];
    $title = $sessionVars["SELL_title"];
    $description = stripslashes($sessionVars["SELL_description"]);
    $pict_url = $sessionVars["SELL_pict_url"];
    $atype = $sessionVars["SELL_atype"];
    $adultonly = $sessionVars["SELL_adultonly"];
    $TPL_item_value = $item_value = $sessionVars["SELL_item_value"];
    $TPL_number_of_bids =$number_of_bids=$sessionVars["SELL_number_of_bids"];
    $TPL_bid_value = $bid_value = $sessionVars["SELL_bid_value"];
    $iquantity = $sessionVars["SELL_iquantity"];
    $buy_now = $sessionVars["SELL_with_buy_now"];
    $buy_now_price = $sessionVars["SELL_buy_now_price"];
    $duration = $sessionVars["SELL_duration"];
    $duration_second = $sessionVars["SELL_duration_second"];
    $minimum_users = $sessionVars["SELL_minimum_users"];
    $relist = $sessionVars["SELL_relist"];
    $increments = $sessionVars["SELL_increments"];
    $customincrement = $sessionVars["SELL_customincrement"];
    $international = ($sessionVars["SELL_international"])?"on":"";
    $sellcat = $_SESSION['sellcat'];
    $private = $sessionVars["SELL_private"];
    if($private != 'y') $private = 'n';
    $sendemail = $sessionVars["SELL_sendemail"];
    $txt = $sessionVars["SELL_txt"];
    $num = $sessionVars["SELL_num"];
    $buy_now_only = $sessionVars["SELL_buy_now_only"];


and...


$auction_id=$sessionVars['SELL_auction_id'];
  } elseif ($sessionVars["SELL_action"] == "reopen") {
    $query =
    "UPDATE BPLA_auctions set
       title = '".addslashes($sessionVars["SELL_title"])."',
       starts = '".$a_starts."',
       starts_second = '".$a_starts_second."',
       description = '".addslashes($sessionVars["SELL_description"])."',
       pict_url = '".addslashes($sessionVars["SELL_pict_url"])."',
       category = ".$sessionVars["SELL_sellcat"].",
       minimum_bid = '".$sessionVars["SELL_minimum_bid"]."',
       reserve_price = '".(($sessionVars["SELL_with_reserve"]=="yes")?$sessionVars["SELL_reserve_price"]:"0")."',
       buy_now = '".(($sessionVars["SELL_with_buy_now"]=="yes")?$sessionVars["SELL_buy_now_price"]:"0")."',
       bn_only = '".$sessionVars["SELL_buy_now_only"]."',
       auction_type = '".$sessionVars["SELL_atype"]."',
       adultonly = '".$sessionVars["SELL_adultonly"]."',
       duration = '".$sessionVars["SELL_duration"]."',
       duration_second = '".$sessionVars["SELL_duration_second"]."',
       minimum_users = ".intval($sessionVars["SELL_minimum_users"]).",
       increment = ".doubleval($sessionVars["SELL_customincrement"]).",

       
       
       international = '".(($sessionVars["SELL_international"])?"1":"0")."',
       ends = '".$a_ends."',
       ends_second = '".$a_ends_second."',
       photo_uploaded = ".(($sessionVars["SELL_file_uploaded"])?"1":"0").",
       quantity = ".$sessionVars["SELL_iquantity"].",
         relist=".intval($sessionVars["SELL_relist"]).",
         relisted=0,
         closed='0',
       private='n',
       item_value = '".$sessionVars["SELL_item_value"]."',
       number_of_bids = '".$sessionVars["SELL_number_of_bids"]."',
       bid_value = '".$sessionVars["SELL_bid_value"]."',       
       suspended='".$SUSPENDED."',";
       $query .= "current_bid=0,
       num_bids=0,
       
       WHERE id = '".$sessionVars["SELL_auction_id"]."'";
    $backtoclosed=true;
    

if($BPLowbidAuction_TESTMODE == 'yes'){

echo $ERR = $ERR_9999;

}else{
    $res=mysql_query($query);
    if (!$res) {
      MySQLError($query);
      exit;
    }

    $auction_id=$sessionVars['SELL_auction_id'];
    $sessionVars["SELL_auction_id_old"]=$auction_id;
    $query = "DELETE FROM BPLA_bids WHERE auction='$auction_id'";
    $res = @mysql_query($query);
    if(!$res) {
      
      MySQLError($query);
      exit;
    }


Thanks in advance for any help and replies! 

Here is one of those purely conceptual questions, which involves no code.  I'm trying to create a select query which among other things, allows a user of a website to search other members who fall within a particular age range.  I have a column in the table where the members' information are stored which records their ages.  Which brings us to the problem.  On any given day, a member's age may increase by one year compared to what it was the previous day, hence the need to update this column periodically.  I can't think of any way to automatically update this column on a daily basis.  So my solution is to run an initial update query every time a member tries to search other members based on age, which updates the age column for all other members, before running the select query which eventually retrieves the desired age range.  This leads to the second problem.  Imagine there are thousands of users using the website.  At any given instance, there could be hundreds of members, trying to search others based on age.  This means hundreds of users will be updating a single column (the age column) in one table at the same time.  Is this feasible? Can it cause the server to crash? Or is there really a more reasonable way to do all of this? Thank you all for taking your time to read this. Appreciate any responses.

have a database with a column of datatype EM (multiple selection, say apples, oranges, grapes)

I want to have a form with checkboxes for those values (apples, oranges, grapes etc) so one can select a breakfast basket type that contains for example, oranges and grapes.

It would pull from the database those baskets with those selection of fruits (specified in the column 'Fruits', which is a EM datatype column. (I have a multiple selection when entering the values for the column.)

Can it be done? Should I make another table with FruitNames?

Looking for help with a small task for my weather station website.
Has already created a MySQL database where values are entered every hour.

Want to see on my website a table with current temperature or other types of measurements for each hour.
The idea is that users select a "Year", "Month" and Which info they want displayed, which is then displayed in an HTML table.
Info that can be chosen from is e.g. temperature, mm rain, air pressure, mm.

A small fee is usually given.

the website:
Se the page

Examples of desired solutions:
http:Sample 1
Sample 2

 

I'm planning on storing RIPEMD-160 passphrase hash's raw binary data in MySQL as BINARY(20).  Right now, I'm not sure if one of the following INSERT approach is more preferrable and more efficient:

Approach (1) - Using mysqli::real_escape_string()
=================================================

Code: [Select]
$binhash = $mysqli->real_escape_string(hash('ripemd160', $passphrase, true));
$mysqli->query("INSERT INTO testtbl (Passphrase) VALUES ('" . $binhash . "')");
This approach saves hash() from converting raw binary data to hexadecimal format, but need to process and escape the binary string.

Approach (2) - Using UNHEX() on MySQL
=====================================

Code: [Select]
$hexhash = hash('ripemd160', $passphrase);
$mysqli->query("INSERT INTO testtbl (Passphrase) values (UNHEX('" . $hexhash . "'))");
This approach needs hash() to convert binary data to hexadecimal but no need for escaping the string and it put extra load on MySQL to convert the hexadecimal back to binary.

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=342695.0

I have a file that is uploaded by the user that has a series of rows of data in an html table.

The columns are always a constant, but the number of rows in the file can change.  These rows of the html table are the only information in the file when I am processing it.

I need to either convert the file to a CSV file or write the values to a CSV list so that I can then insert them into a database.

Any suggestions would be wonderful...I've spent the last three hours spinning my wheels.

Here is an example of one row of data from the file:

Code: [Select]
<tr><td bgcolor=#dddddd class=dataFT >5 (Excellent)</td><td bgcolor=#dddddd class=dataFT >5 (Excellent)</td><td bgcolor=#dddddd class=dataFT >5 (Excellent)</td><td bgcolor=#dddddd class=dataFT >&nbsp;</td><td bgcolor=#dddddd class=dataFT >&nbsp;</td><td bgcolor=#dddddd class=dataFT >DATA</td><td bgcolor=#dddddd class=dataFT >1015020</td><td bgcolor=#dddddd class=dataFT >155498322</td><td bgcolor=#dddddd class=dataFT >DATA</td><td bgcolor=#dddddd class=dataFT >4731751</td><td bgcolor=#dddddd class=dataFT >2011-09-25 11:18:33  (-5:00)</td><td bgcolor=#dddddd class=dataFT >2011-09-25 12:16:50  (-5:00)</td><td bgcolor=#dddddd class=dataFT >0:58:17</td><td bgcolor=#dddddd class=dataFT >0:58:16</td><td bgcolor=#dddddd class=dataFT >DATA</td><td bgcolor=#dddddd class=dataFT >DATA</td><td bgcolor=#dddddd class=dataFT >DATA</td><td bgcolor=#dddddd class=dataFT >Resolved</td></tr>

I want to have a search product feature, but I would like members to be able to search multiple fields in one go i.e.  product_code, Product_name in one MySQL query.

The thing is, members have to be logged on, so the query must also only show results relating to that specific member, via the session[member_ID], my current query for listing products for that specific member is :

Code: [Select]
$sql = "SELECT productId, productCode, image, name, price, stock_level FROM product_inventory WHERE memberr_ID = '" . $_SESSION['SESS_mem_ID'] . ";
How would I change the above into a search query to search for productcode, productname and still only show results beloging to this member using the session data ? 

all help appreciated

So I'm trying to create an Array from a comma separated string containing array{s}. How can I take a string full of comma separated hex values such as : 

<?php
    $myStr = (
        "00ffff", "00cccc", "009999", 
        "007777", "004444", "001111", 
        "01ffff", "01cccc", "019999"
    );
?>

& turn it into 3 separated arrays like : 

<?php
    $myNewArrs = [
        Array1 ( 3 ), 
        Array2 ( 3 ), 
        Array3 ( 3 ), 
    ];
?>

which REALLY looks like : 

<?php
    $myNewArrs = [
        Array ( "00ffff", "00cccc", "009999" ), 
        Array ( "007777", "004444", "001111" ), 
        Array ( "01ffff", "01cccc", "019999" ), 
    ];
?>

?

Any assistance is most-definitely appreciated!

Thank you & have a great afternoon!

 

 

Edited May 27 by AquariaXI