|
PHP - Truncate A Mysql Table But Exclude First Column
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
Similar TutorialsHey guys, so I have this script:
<?php $databasehost = "localhost"; $databasename = "import"; $databasetable = "import"; $databaseusername="import"; $databasepassword = "password"; $fieldseparator = ","; $lineseparator = "\n"; $csvfile = "test.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()); } $affectedRows = $pdo->exec(" LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." REPLACE INTO TABLE `$databasetable` FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)." LINES TERMINATED BY ".$pdo->quote($lineseparator)); echo "Loaded a total of $affectedRows records from this csv file.\n"; ?> 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 want to run a query on table 1 but exclude records if the field v3 matches the field "mrn" in table 2: I was told to use LEFT OUTER JOIN but this doens't work: Code: SELECT * FROM pts LEFT OUTER JOIN p_list ON pts.v3 = p_list.mrn WHERE pts.v3 IS NULL When I do that nothing is displayed. I don't know if the "IS NULL" term is the problem. Can someone help me with what the php code is to make that query work? 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 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. 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> 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 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! This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=322237.0 I have a kind of search and react on my site. The user can search for a number in a column as below $search = mysqli_real_escape_string($link, $_POST["query"]); $query = "SELECT * FROM $table[$i] WHERE oemnr LIKE '".$search."%' ORDER BY model";
The code works fine but I want to expand the function to get match for other numbers also. The oemnr column today can be 12345 which shows up if the user type 12.... I want to expand the oemnr column as 12345/03124/34713, numbers separated with / or another character. If the user type 03... or 34... I want to find and show 12345 because it is on the same row and corresponds to the numbers on the same row. Is it possible to do in mysql? In php that kind of operation can be done with the explode function but I don't know how to use with mysql. Edited February 14 by jiggahave 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? 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. My Php Buddies, I have mysql tbl columns these:
id: Now, I want to display their row data by excluded a few columns. Want to exclude these columns: date_&_time account_activation_code account_activation_status id_verification_video_file_url password
So, the User's (eg. your's) homepage inside his account should display labels like these where labels match the column names but the underscores are removed and each words' first chars CAPITALISED:
Id: 1
For your convenience only PART 1 works. Need help on Part 2 My attempted code:
PART 1
<?php
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Query to get columns from table
$query = $conn->query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'members' AND TABLE_NAME = 'users'");
while($row = $query->fetch_assoc()){
$result[] = $row;
}
// Array of all column names
$columnArr = array_column($result, 'COLUMN_NAME');
foreach ($columnArr as $value) {
echo "<b>$value</b>: ";?><br><?php
}
?>
PART 2
<?php
//Display User Account Details
echo "<h3>User: <a href=\"user.php?user=$user\">$user</a> Details</h3>";?><br>
<?php
$excluded_columns = array("date_&_time","account_activation_code","account_activation_status","id_verification_video_file_url","password");
foreach ($excluded_columns as $value2)
{
echo "Excluded Column: <b>$value2</b><br>";
}
foreach ($columnArr as $value)
{
if($value != "$value2")
{
$label = str_replace("_"," ","$value");
$label = ucwords("$label");
//echo "<b>$label</b>: "; echo "$_SESSION[$value]";?><br><?php
echo "<b>$label</b>: "; echo "${$value}";?><br><?php
}
}
?>
PROBLEM: Columns from the excluded list still get displayed. Edited November 19, 2018 by phpsaneHi
I am very new to PHP & Mysql.
I am trying to insert values into two tables at the same time. One table will insert a single row and the other table will insert multiple records based on user insertion.
Everything is working well, but in my second table, 1st Table ID simply insert one time and rest of the values are inserting from 2nd table itself.
Now I want to insert the first table's ID Field value (auto-incrementing) to a specific column in the second table (only all last inserted rows).
Ripon.
Below is my Code:
<?php
$con = mysql_connect("localhost","root","aaa");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("ccc", $con);
$PI_No = $_POST['PI_No'];
$PO_No = $_POST['PO_No'];
$qry = "INSERT INTO wm_order_entry (
Order_No,
PI_No,
PO_No)
VALUES(
NULL,
'$PI_No',
'$PO_No')";
$result = @mysql_query($qry);
$val1=$_POST['Size'];
$val2=$_POST['Style'];
$val3=$_POST['Colour'];
$val4=$_POST['Season_Code'];
$val5=$_POST['Dept'];
$val6=$_POST['Sub_Item'];
$val7=$_POST['Item_Desc'];
$val8=$_POST['UPC'];
$val9=$_POST['Qty'];
$N = count($val1);
for($i=0; $i < $N; $i++)
{
$profile_query = "INSERT INTO order_entry(Size,
Style,
Colour,
Season_Code,
Dept,
Sub_Item,
Item_Desc,
UPC,
Qty,
Order_No
) VALUES( '$val1[$i]','$val2[$i]','$val3[$i]','$val4[$i]','$val5[$i]','$val6[$i]','$val7[$i]','$val8[$i]','$val9[$i]',LAST_INSERT_ID())";
$t_query=mysql_query($profile_query);
}
header("location: WMView.php");
mysql_close($con);
?>
Output is attached.Hi there,
I'm trying to truncate some text by 15 words which is then followed by a "..."
This is the line that outputs all the text:
wpjm_the_job_description(); I've tried this, but nothing is happening.
<?php $desc=wpjm_the_job_description(); echo substr($desc, 0, 15); ?> Any ideas what I have wrong?
Thanks Edited May 9, 2018 by requinixfixing bbcode Hello all. I am finishing up a paypal IPN php script and was wondering how I can take some data and cut some of it off before I enter it into the mysql database? Heres what I have. $payment_date = HH:MM:SS DD Mmm YY, YYYY PST This comes from paypal. I want to in my code take that and automatically cut off the HH:MM:SS part. So my question is, how can I take a known piece of data and automatically trim the first 9 spots off of it? Something like: $payment_date = HH:MM:SS DD Mmm YY, YYYY PST Minus 1 thru 9 of $payment_date = $result $payment_date2 = $result Any help? Thanks! Simple question but I couldn't find any straight forward answers.
I have a customer details table with: customer ID, name, address, email.
I also have an delivery table with delivery ID, customer ID, order ID.
How can I link the customer ID column from the customer table to the delivery table so that when changes occur on the customer table they subsequently alter all other tables?
I know it might use foreign keys but I couldn't find any online resource that explained how to use them properly.
Cheers
When I try to run the code below with Code: [Select] if ( $user->uid ) it works just dandy. But if I use Code: [Select] if ( !$user->uid ) it puts the number one a little bit below my truncated paragraph. It only started doing this after I put the Code: [Select] $login = print("Log in to see full article!");. It worked normally (besides the fact that it didn't say log in to see full article) And for description, I have 400 words of Lorem Ipsum, but you guys dont need to see that so I put elipses. Code: [Select] <head> <?php // this signifies how to truncate function myTruncate($string, $limit, $break=".", $pad="...") { // return with no change if string is shorter than $limit if(strlen($string) <= $limit) return $string; // is $break present between $limit and the end of the string? if(false !== ($breakpoint = strpos($string, $break, $limit))) { if($breakpoint < strlen($string) - 1) { $string = substr($string, 0, $breakpoint) . $pad; } } return $string; } ?> <title>truncate test</title> </head> <body> <?php $description = 'Lorem. ipsum dolor sit amet...'; // This makes it so it only applies to non logged in users if ( !$user->uid ) { //Replace 'XXX' with your number (Probably 500) $description = myTruncate($description, xxx); $login = print("Log in to see full article!"); } echo "<p>$description</p>"; echo "<p>$login</p>"; ?> Is there a way to use php to make an automatic column addition to a table? like if I had a comment form and want to save the replys in a database, I would want a column for each reply, is there a way to make a new column each time someone submits the form? Php Buddies, Just how do you insert an uploaded img via html for ($_POST) to mysql tbl and what type of column should it be ? Also, how to link the img from the mysql column to a webpage ? Following html links the img from a folder/directory to a webpage: <a href="../html-link.htm"><img src="flower.jpg" style="width:82px; height:86px" title="White flower" alt="Flower"></a> |
|||||||