array set as a column name from an array using php mysql

Question

I have an foreach loop that is creating a table with new column names from an array collected from a site. At the moment only the first element of the array is being set as a column name, can someone help?

H Here is my code:

<?php
include 'simple_html_dom.php';
include 'connection.php';

function getsquad($url, $tablename){

$html = file_get_html($url);

$player_fromsite = array();

$space = ' ';
$replacespace = '_';

$player = array();

foreach($html->find('td[align=left]') as $element) {
   if ($element->children(0)) { // work only when children exists
          array_push($player_fromsite ,$element->children(0)->innertext);
   }
}

//$player[] = str_replace($space, $replacespace, $player_fromsite);
//unset($player_fromsite);

$length = count($player);

foreach($player_fromsite as $player_name) {
  mysql_query("CREATE TABLE " . $tablename . "(" . str_replace($space, $replacespace,     $player_name) . " VARCHAR(30))") or die(mysql_error());   
}

echo "Table Created!";

}

$Squad = new squad();
$Squad->getsquad('site', 'Ars');

?>

any spaces are replace with a "_", in the foreach loop

BluePsyduck · Accepted Answer · 2013-12-15 17:38:00Z

1

I am not quite sure if I correctly understand what you want: In $player_fromsite you have some strings, which should become columns of the new table with the name from $tablename, whereas all columns are VARCHAR(30).

If so, replace your last foreach loop with the following:

$columns = array();
foreach ($player_fromsite as $player_name) {
    $columns[] = '`' . str_replace($space, $resplacespace, $player_name) . '` VARCHAR(30)';
}
$query = 'CREATE TABLE `' . $tablename . '` (' . implode(',', $columns) . ')';
mysql_query($query) or die(mysql_error());

The foreach loop basically prepares all the columns, which afterwards get concatenated to the actual query which is send to the database.

So for exmaple let $player_fromsite = array('foo', 'bar'), you would end up with the query

CREATE TABLE `Ars` (`foo` VARCHAR(30),`bar` VARCHAR(30));

edited Dec 15, 2013 at 17:38

answered Dec 15, 2013 at 17:10

BluePsyduck

1,1419 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Nic Over a year ago

I have an error message saying "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '–zil VARCHAR(30),Bacary_Sagna VARCHAR(30),Jack_Wilshere VARCHAR(30),Santi_Cazo' at line 1" these are the players names I'm trying to make into columns. It's the closest so far

BluePsyduck Over a year ago

I edited my answer and added some backticks to the query for table and column names, can you try it again?

Nic Over a year ago

Wow, this worked. I been working on this for couple of day and so many people say it wasn't possible but here the answer. Thanks for the code

Nic Over a year ago

What does the backticks dO?

BluePsyduck Over a year ago

With backtics it is possible to have some characters in a table or column name, which normally are forbidden as of syntax restrictions. So for example where SELECT * FROM -foo is a syntax error, adding backticks around the table name makes it a wellfromed query. See also dev.mysql.com/doc/refman/5.0/en/identifiers.html

Collectives™ on Stack Overflow

array set as a column name from an array using php mysql

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related