implode with more variables by sql query

[WesleyA]

I have a problem with passing 2 arrays in one mysql query.

 

I have to use foreach and implode 2 times before having the query put in mysql.

 

Is it possible to use a syntax like

$varc = implode(", " , $vara, $varb)

or

            foreach($x as $vara, $varb)

I tried it but it didnt work, so I wonder what alternatives there are?

[dsonesuk]

merge them

 

$varc = implode(", ", array_merge($vara, $varb));

[WesleyA]

Okay, but what happens then because I want to use the next query

      $sql = "INSERT INTO table_name (colname1, colname2) VALUES ('$vara', $varb ) ";

What I try to say is that I have 2 arrays which have to end up in 2 columns but will that work if you make one array like $varc of it?

[dsonesuk]

I suppose you could do that, but! they should end giving same result as '$vara', $varb but i think you mean '$vara', '$varb'

 

$varc = "'" . implode(", ", $vara) . "', '" . implode(", ", $varb) . "'";

 

so

 

$vara = array('a', 'b', 'c');$varb = array('d', 'e', 'f');

 

meaning end result of $varc will look like

 

VALUES('a, b, c', 'd, e, f')

[WesleyA]

I tried it but it didnt really work.

 

it has to do with wanting these arrays on the same row. What you say is true, namly that you get an entire array but it is added completely to 1 row.

Edited June 9, 2015 by WesleyA

[WesleyA]

The condition section now looks like this:

                        $name_A = $_POST['name_A'];			$name_B = $_POST['name_B'];									$newvar2 = implode (", " , $name_A);			$newvar3 = implode (", " , $name_;									foreach ($name_A as $newvar2) {																		$sql = "INSERT INTO table_name (name_A, name_                                        VALUES ('$newvar2', '$newvar3') ";								if ($conn->query($sql) === TRUE) 								{  // to be continued

resulting in putting an array in the name_B, but name_A is now correct which is inserting only just one string element each time.

 

 

If I understand PHP a bit then inthis script foreach is the term that for each arrayname every element is .. then the statement

 

 

But this only happens for $name_A as $newvar2, and that's it.

 

This doesnt happen for $name_B and $newvar3.

 

Doing it outside the loop with a second for eacht condition is adding to a new set of five rows. (if you make 2 separate sql queries for $newvar2 and $newvar3).

Edited June 9, 2015 by WesleyA

[justsomeguy]

Are you trying to insert every combination of values in the 2 arrays, or matching values (the first values in one row, the second values in another, etc).

[WesleyA]

I made a screen print, the phpmyadmin settings are in my own dutch language but it helps explaining what my puprpose is:

 

 

the right column contains the entire array of five links, while it shoudl contain only one. The name (linkoms) should correspondent with the linkaddress (linkadd).

Edited June 10, 2015 by WesleyA

[dsonesuk]

Why are you placing all links in a array? where are these collected from? form input? you could create table with these predefined a id number, linkoms and corresponding single link, then all you have to do is use id ref.

[WesleyA]

Why are you placing all links in a array? where are these collected from? form input? you could create table with these predefined a id number, linkoms and corresponding single link, then all you have to do is use id ref.

 

Okay I'll do that, but what exactly is meant with id ref?

 

And what should the table look like what kind of counter is regularly used in these cases?

Edited June 10, 2015 by WesleyA

[WesleyA]

Isnt it more useful to create a column extra.

 

linkoms and linkadd are also columns, so id_num could also be a column do you agree with that?

[dsonesuk]

I don't know how exactly these tie together, does someone select these linkoms? if they do you can have a id ref (number) post ($_POST[]) to give the id they selected, this id value would be related to id ref number which auto increments every time you add a new wine? to give you a unique id ref (linkoms) with singular related url linkadd.

 

example you have

option text 'white wine' description that will pass a id value 1

option text 'red wine' description that will pass a id value 2

 

which will relate to table with

 

id | linkoms | linkadd

1 | white wine | www.whitewine.com

 

or

 

2 | red wine | www.redwine.com

[justsomeguy]

If you have parallel arrays of the same length and you're trying to get elements from each one then use a for loop:

 

 

for ($i = 0; $i < count($array1); $i++) {  $val1 = $array1[$i];  $val2 = $array2[$i];}

[WesleyA]

 

If you have parallel arrays of the same length and you're trying to get elements from each one then use a for loop:

for ($i = 0; $i < count($array1); $i++) {  $val1 = $array1[$i];  $val2 = $array2[$i];}

instead of foreach?

[WesleyA]

Is it possible to make unique id's or keys (are they the same?) for 1) tables, 2) columns, 3) rows?

[justsomeguy]

instead of foreach?

Yes. Foreach loops over a single array. If you need to access elements from multiple arrays then use for instead.

Is it possible to make unique id's or keys (are they the same?) for 1) tables, 2) columns, 3) rows?

Tables and columns don't have IDs. You can make a unique ID on rows if you set up a column to be either a unique or primary key.

[WesleyA]

Yes. Foreach loops over a single array. If you need to access elements from multiple arrays then use for instead.Tables and columns don't have IDs. You can make a unique ID on rows if you set up a column to be either a unique or primary key.

 

Yes I understand that a bit more. I managed to creat a primary key which is incremented automatically. SO the rows are auto incremented.

 

But I want more.

 

I think it's useful to give a table also a unique number and I would want to figure out a way I which this is possible. I hope it is namly, and I think it's illogical when mysq doesnt give the possibilities.

[justsomeguy]

I think it's useful to give a table also a unique number

I don't see the point. A table already has a unique identifier - the name of the table. You don't need 2 unique identifiers to refer to the same thing.

I think it's illogical when mysq doesnt give the possibilities.

That's because the folks who designed MySQL didn't see the utility of doing something like that, so you should seek to understand why. The answer is because a table is already uniquely defined. Columns are also uniquely defined by name, as are databases. You can design a table schema any way you like, you can design it so that it's not possible to uniquely identify a single row, or you can design it so that you can. Tables, columns, and databases are always uniquely defined. There's not an option or really a reason why you would have multiple tables or columns with the same names, they all need to be unique within their parent.

[WesleyA]

I found this script here: http://www.w3schools.com/php/php_mysql_prepared_statements.asp

 

I shall quote it

Example (MySQLi with Prepared Statements)     <?php     $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);           }       // prepare and bind                $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)"        );         $stmt->bind_param("sss", $firstname, $lastname, $email);          // set parameters and execute      $firstname = "John";      $lastname = "Doe";      $email = "john@example.com";       $stmt->execute();      $firstname = "Mary";      $lastname = "Moe";      $email = "mary@example.com";      $stmt->execute();      $firstname = "Julie";      $lastname = "Dooley";      $email = "julie@example.com";      $stmt->execute();        echo "New records created successfully";       $stmt->close();        $conn->close();        ?>

A set up like this might work for me maybe.

 

But in this script isnt any definition to find for instance for $stmt. I tried if(isset) but I'm not sure if that is the solution. How is a variable like $stmt defined in thi matter?

[Ingolme]

The variable $stmt was created on this line:

$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)"        );

[WesleyA]

Because one arry is done exactly as it supposed to be inserted and one not I have the idea that the for each loop should be extended.

 

The options you have given result in the same problem, namly that 1 array is inserted okay, and one not.

 

Maybe I can indeed combine it with the autoincrement option Do you have any idea how programmers would employ an autoincrement table variable in a foreach loop.

 

I had a construction with 2 for each loops but this resluted in adding the other array on extra rows.

 

So instead of the wrong example I uploaded; I would like to have the following output

 

 

linkoms: ---------------- linkadd

++++++++++++++++++++++++++++++++++++++++++++++++

wine --------------------www.wine.com

white wine -------------www.whitewine.com

red wine ---------------www.redwine.com

etc.

 

 

 

 

 

[justsomeguy]

What do the arrays look like? Use print_r to print them and paste that here.

[WesleyA]

I already fixed the issue I used mysqli_real_escape_string