METANIT.COM

Code example for removing strings in a MySQL column using PHP could be the

To auto-increment, it is suggested to let the database handle it. For MySQL, declare it with AUTO_INCREMENT on the ID field and for Postgres, set the data type to serial. To separate each line, use PHP explode function. Here’s a little example using PDO, which is the recommended method for handling database connections. To split the lines and insert them in the database, one can use something like this. For MySQL, remove the last character. You can find a great tutorial for this. To extract numbers from a string, create a function or use the function in common_schema. If you don’t want to write your own stored function, it’s pretty simple using the common_schema function.

Removing numbers from string in mysql

My recommendation is for you to generate a User Define Function on your own. There is an excellent tutorial available that can assist you in this task.

DELIMITER $$ DROP FUNCTION IF EXISTS `uExtractNumberFromString`$$ CREATE FUNCTION `uExtractNumberFromString`(in_string varchar(50)) RETURNS INT NO SQL BEGIN DECLARE ctrNumber varchar(50); DECLARE finNumber varchar(50) default ' '; DECLARE sChar varchar(2); DECLARE inti INTEGER default 1; IF length(in_string) > 0 THEN WHILE(inti 0 THEN SET finNumber=CONCAT(finNumber,sChar); ELSE SET finNumber=CONCAT(finNumber,''); END IF; SET inti=inti+1; END WHILE; RETURN CAST(finNumber AS SIGNED INTEGER) ; ELSE RETURN 0; END IF; END$$ DELIMITER ; 

After creating the function, eliminating the numbers from the string becomes effortless. For instance,

SELECT uExtractNumberFromString(Season) FROM TableName 

If you’re not interested in creating your own stored function for this task, you can easily achieve it by utilizing the common_schema’s replace_all() function.

To illustrate, the values in the Season column of your table will have all digits from 0 to 9 eliminated with this action.

select common_schema.replace_all(season,'0123456789','') from your_table 

In case the final two characters of your data are digits, you have the option to utilize.

select substr(season,1,length(season)-2) from tbl; 

PHP MySQL Select Data, The following example shows the same as the example above, in the MySQLi procedural way: Example (MySQLi Procedural)

How to delete a comma separated value in php mysql

Note:

• The column field in your table should not be used to store file names or data in an inappropriate manner.
• It is advisable to rectify your post by adding the code you are presently working on.

The recommended approach would have been to place it in the designated image section, similar to this format:

| image | | 1.jpg | | 2.jpg | | 3.jpg | | 4.jpg | 

Answer:

In case you’re facing difficulty dealing with it and all you want is to eliminate the 2.jpg from the provided string, you can opt for the explode() function in PHP. Feel free to refer to its usage here. (Don’t get overwhelmed by the code, as I’ve included comments to clarify the purpose of each line.)

Simply replace the appropriate table , column , and variable names as needed using this code.

if(isset($_REQUEST['id'])) < $userDelete = $_REQUEST['val']; $newString = ""; $rs = mysqli_query($YourDBConnection,"SELECT id, data FROM arr"); while($row = mysqli_fetch_array($rs))< $id = $row["id"]; $data = $row["data"]; $exploded = explode(",",$data); $counter = count($exploded); for($x = 0; $x/* END OF IF THE REQUESTED TO DELETE IS NOT THE SAME WITH EXPLODED DATA */ > /* END OF FOR LOOP */ mysqli_query($YourDBConnection,"UPDATE arr SET data = '$newString' WHERE > /* END OF WHILE LOOP */ > /* END OF ISSET REQUEST ID */ 

Recommendation:

By correctly placing it in your column field, you can easily eliminate 2.jpg from your database using DELETE query .

DELETE FROM yourTableName WHERE image = '2.jpg' 

To avoid permanent removal of data from your database, I suggest adding a new column that specifies the action to be taken for the picture, whether it needs to be hidden or displayed on the front end.

Consider the following scenario where a column, denoted as action , can only take on the values of either 1 or 0 . In this context, 1 activates to display the image while 0 is responsible for hiding the image.

To accomplish this task, you should choose the image column from the database table and subsequently compose the specified code.

$oldimages = '1.jpg,2.jpg,3.jpg,4.jpg'; $replaceImage = '2.jpg'; $newImages = str_replace($replaceImage.",", "", $oldimages); // then write update query. mysqli_query($conn,"UPDATE images SET images = '$newImages' WHERE

MySQL SUBSTRING() Function, string: Required. The string to extract from: start: Required. The start position. Can be both a positive or negative number. If it is a positive number, this function …

What is the best way to loop through string and insert into mysql column?

To automate the increment feature, you can rely on the database to handle it. In MySQL, you can simply declare it with AUTO_INCREMENT on the id field. Similarly, for Postgres, you may set the data type to serial. To distinguish each line, utilize the php explode function separator.

prepare($query); foreach(explode(',', $data) as $r) < // user array($r) for php 5.3 or lower $st->execute([$r]); > 

PDO is the preferred way of managing database connections and is utilized in this approach.

An option for insertion in the db can be provided by something similar to this, which should also result in their separation.

How can I remove part of a string in PHP?, You can use str_replace (), which is defined as: str_replace ($search, $replace, $subject) So you could write the code as: $subject = ‘REGISTER 11223344 here’ ; …

Remove last character in a string mysql

mysql remove last character

PHP str_replace() Function, The str_replace () function replaces some characters with some other characters in a string. This function works by the following rules: If the string to be searched is … Code sampleFeedback

Источник

Php mysql delete string

Для удаления данных применяется sql-команда DELETE :

DELETE FROM Таблица WHERE столбец = значение

Для удаления данных возьмем использованную в прошлых темах таблицу Users со следующим определением:

CREATE TABLE Users (id INTEGER AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30), age INTEGER)

Объектно-ориентированный стиль

Вначале определим для вывода всех объектов из БД скрипт index.php :

     
Список пользователей
 connect_error)< die("Ошибка: " . $conn->connect_error); > $sql = "SELECT * FROM Users"; if($result = $conn->query($sql))< echo ""; foreach($result as $row)< echo ""; echo ""; echo ""; echo ""; echo ""; > echo "
Имя
Возраст
 
" . $row["name"] . "
" . $row["age"] . "
  
"; $result->free(); > else< echo "Ошибка: " . $conn->error; > $conn->close(); ?>  

В таблицы для каждой строки определена форма, которая посылает данные в POST-запросе скрипту delete.php . Чтобы передать в delete.php идентификатор удаляемого объекта, на форме определено скрытое поле для хранения id объекта.

Обратите внимание, что в данном случае применяется не ссылка для удаления типа

которая оправляет данные в GET-запросе, а именно форма, которая отправляет данные в POST-запросе. Почему? Подобные GET-запросы потенциально небезопасны. Допустим, нам пришло электронное письмо, в которое была внедрена картинка посредством тега:

В итоге при открытии письма 1-я запись в таблице может быть удалена. Уязвимость касается не только писем, но может проявляться и в других местах, но смысл один — GET-запрос к скрипту, который удаляет данные, несет потенциальную уязвимость.

Теперь определим сам скрипт delete.php , который будет выполнять удаление:

connect_error)< die("Ошибка: " . $conn->connect_error); > $userid = $conn->real_escape_string($_POST["id"]); $sql = "DELETE FROM Users WHERE "; if($conn->query($sql)) < header("Location: index.php"); >else< echo "Ошибка: " . $conn->error; > $conn->close(); > ?>

В данном случае скрипт получает через POST-запрос значение id и по этому идентификатору выполняет удаление. После чего происходит переадресация на скрипт index.php .

Процедурный стиль

     
Список пользователей
 $sql = "SELECT * FROM Users"; if($result = mysqli_query($conn, $sql))< echo ""; foreach($result as $row)< echo ""; echo ""; echo ""; echo ""; echo ""; > echo "
Имя
Возраст
 
" . $row["name"] . "
" . $row["age"] . "
  
"; mysqli_free_result($result); > else < echo "Ошибка: " . mysqli_error($conn); >mysqli_close($conn); ?>  

 $userid = mysqli_real_escape_string($conn, $_POST["id"]); $sql = "DELETE FROM Users WHERE "; if(mysqli_query($conn, $sql)) < header("Location: index.php"); >else < echo "Ошибка: " . mysqli_error($conn); >mysqli_close($conn); > ?>

Источник

PHP MySQL Delete Data

Delete Data From a MySQL Table Using MySQLi and PDO

The DELETE statement is used to delete records from a table:

Notice the WHERE clause in the DELETE syntax: The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

To learn more about SQL, please visit our SQL tutorial.

Let’s look at the «MyGuests» table:

id	firstname	lastname	email	reg_date
1	John	Doe	john@example.com	2014-10-22 14:26:15
2	Mary	Moe	mary@example.com	2014-10-23 10:22:30
3	Julie	Dooley	julie@example.com	2014-10-26 10:48:23

The following examples delete the record with in the «MyGuests» table:

Example (MySQLi Object-oriented)

$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDB»;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) die(«Connection failed: » . $conn->connect_error);
>

// sql to delete a record
$sql = «DELETE FROM MyGuests WHERE ($conn->query($sql) === TRUE) echo «Record deleted successfully»;
> else echo «Error deleting record: » . $conn->error;
>

Example (MySQLi Procedural)

$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDB»;

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) die(«Connection failed: » . mysqli_connect_error());
>

// sql to delete a record
$sql = «DELETE FROM MyGuests WHERE (mysqli_query($conn, $sql)) echo «Record deleted successfully»;
> else echo «Error deleting record: » . mysqli_error($conn);
>

Example (PDO)

$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDBPDO»;

try $conn = new PDO(«mysql:host=$servername;dbname=$dbname», $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// sql to delete a record
$sql = «DELETE FROM MyGuests WHERE // use exec() because no results are returned
$conn->exec($sql);
echo «Record deleted successfully»;
> catch(PDOException $e) echo $sql . «
» . $e->getMessage();
>

After the record is deleted, the table will look like this:

id	firstname	lastname	email	reg_date
1	John	Doe	john@example.com	2014-10-22 14:26:15
2	Mary	Moe	mary@example.com	2014-10-23 10:22:30

Источник