Select mysql column (which is array) where other column is defined,:- and insert it into php variable

php mysql fetch array multiple rows
store mysql query results in php array
how to store values to array from mysql database in php
how to fetch single column from database in php
how to fetch data from database and store in variable in php
mysqli fetch array
mysql_fetch_assoc all rows
php mysql get column names from query

I have a two columns in database:- copy (which is array) and bookid. My code is;

$query = mysqli_query($db, "select copy from book_outward WHERE bookid like 'B1'");
while ($row = mysqli_fetch_array($query)) {
    $copyid = $row['copy'];
}

Database shows like this

+----------------+
| id copy bookid |
+----------------+
| 1  1    B1     |
| 2  2,3  B1     |
| 3  4    B1     |
| 4  2    B2     |
+----------------+

but it stores only last values which was entered in 'B1'. I also tried

$copyid[] = $row['copy'];

but in this case I have to change array keys manually every time.

My aim is to insert copy into column bookid='B1' and before it has to make sure that only UNIQUE values can be stored in database for B1.

HTML :-

<input type="text" name="bookid" />
<input type="text" name="copies[]" />

PHP code for inserting:-

$book_id = $_POST['bookid']; 
$copies = implode(',',$_POST['copies']);
$result = mysqli_query($db, "insert into book_outward(bookid,copy) values ('$book_id','$copies')");

As some of the comments mention it it not the best way to to it but it is possible.

You can obtain all the copyid data by:

$query = mysqli_query($db, "select copy from book_outward WHERE bookid like 'B1'");
$copyid = "";
while ($row = mysqli_fetch_array($query)) {
    $copyid .= $row['copy'] . ",";
}
$copyidsFromDB = explode(",",rtrim($copyid , ','));

After that you can check if what you got in the request are in there using array_intersect:

$copies = $_POST['copies']
// if not an array use: $copies = explode(",", $_POST['copies'])
if (count(array_intersect($copies, $copyidsFromDB) == 0) 
    // insert to DB

mysql_fetch_field - Manual, mysql_fetch_field — Get column information from a result and return as an See also MySQL: choosing an API guide and related FAQ for more of the table the column belongs to, which is the alias name if one is defined variable is only set to 1 if the primary key on the table is only on that 1 field. $T_Return=array(); So to insert an array into a mysql database you have to convert it to an sql statement. This can be done manually or by a library. The output should be an INSERT statement. Here is a standard mysql insert statement. INSERT INTO TABLE1(COLUMN1, COLUMN2, .) VALUES (VALUE1, VALUE2..)

Solved by myself by adding one line only

`$copies2 = explode(",",rtrim($copies , ','));`

before array_intersect Thanks code helps greatly.

Beginning MySQL, The while command tells PHP to continue to execute the block of commands as long as the The condition in this case is defined by $row = mysql_fetch_array($​result). function is placed in the $row variable, which is an array of the columns. Each time the while command is executed, a new row is inserted in the variable. 10 Answers 10. you could loop through the array, and create a new one, like so: There is no function to do this using the mysql extension, you can do this: It is apparently marginally faster to fetch the columns in a numerically indexed array, and there is no real benefit here to having the associative array format.

So, sounds to me like the issue is the original data DB schema has some issues.

A sql-like DB is not a great place to put 'array' style value. It already has a mechanism for doing this: a link table.

That'd save you the trouble of having to manually shuffle around your primary keys in this table. It's way easier to check and validate.


If you HAVE to do this via php code, for example you don't have SQL access, like in a controlled build environment, you should take advantage of 'string keys' to on your $copyid, (hint rename to $copyidmap) to group together like pieces of data and preserve key relationships. so the following would be unique: map->{bookId}->{copyid}->{id} // Where id is that primary table id. Obviously, validate and do your undefined array assignments.

mysql_fetch_array - Manual, See also MySQL: choosing an API guide and related FAQ for more information. The type of returned array depends on how result_type is defined. To access the other column(s) of the same name, you must use the numeric index of the I have found a way to put all results from the select query in an array in one line. Append variable and array items after a record array search ; how to make 2 values textfield inserted into one column in database in php ; array shift on 2 dim array ; Colour Conversion Class ; alternate array values ; display array values ; single mysql query to insert columns values from one table to another ; Insert Checkbox Values into

Dreamweaver MX: PHP Web Development, control structures, PHP, 221 foreach, 222 if, 222 count() functions, MySQL, 186 Application, 16 connecting to database, site definition, 71 databases, MySQL, 32, 227 built-in PHP tag/function/variable references, 228 inserting tag/seeing its 58 columns, MySQL tableslrecordsets selecting, SELECT, 169 all columns at  I've been searching for this everywhere, but still can't find a solution: How do I get all the values from a mySQL column and store them in an array? For eg: Table Name: Customers Column names:

Insert query in mysql using php variables, This lets you insert data from a PHP array into your MySQL database table. SELECT INTO variable syntax: c1, c2, and c3 are columns or expressions that you want You can name a variable with the value stored in another variable. To do this, you store the value in a MySQL user-defined variable in the first statement  The preceding SELECT statement assigns a column value to a variable, but variables also can be assigned values from arbitrary expressions. The following statement determines the highest sum of the arms and legs columns in the limbs table and assigns it to the @max_limbs variable: mysql>SELECT @max_limbs := MAX(arms+legs) FROM limbs;

Beginning PHP, Apache, MySQL Web Development, What we are doing here is looping through $_POST and setting each variable that each button is named action and that each one has a different value. In plain English: “Insert the values $zip, $city, and $state into the columns id, city, an insert without first using a SELECT query to see if the data is already in the table. We created a table variable named myTableVariable and we inserted 3 rows and then we did a select in the table variable. The select will show the following values: Now, we will show information of the table Person.person of the adventureworks database that match with the nameS of the table variable:

Comments
  • what is the data type for copies in your db ?
  • @suresh datatype is "VARCHAR"
  • bookid = 'B1' or bookid like '%B1%'
  • Never store values as What does copy (which is array) means. can you Show sample data
  • So you can have multiple row with bookid as B1 but each has to have different value in the copy column?
  • thanks. Much help provide but last step "count(array_intersect)" is not working. it is not checking for duplicates values in both arrays. It will be really appreciated if intersect part also starts working.
  • Solved by myself by adding one line only $copies2 = explode(",",rtrim($copies , ',')); before array_intersect Thanks code helps greatly.