Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have the following string:

$result = "category,date,product,cost
car,10/10/2005,toyota,3000
bike,12/12/2007,yamaha,1000";

I need to arrange the string into the best format to insert into a database (needs to be efficient as there might be lots of data also first row will not be needed).

The string is on new lines, so I can:

$n = explode("\n", $result);

which puts it in this format:

[0] => category,date,product,cost
[1] => car,10/10/2005,toyota,3000
[2] => bike,12/12/2007,yamaha,1000

now how would i go about formatting this to insert in a db, once again the solution needs to be efficient (for performance), also I would like the first row to not be inserted and finally on the insert I wish for the date to not be inserted.

Hope it makes sense, thanks for the help in advance.

--------edit---------------

The table (item) is structured with the following fields:

category, product, cost

thanks

share|improve this question
1  
we don't know your db structure, so don't know how to format anything – Dagon Feb 21 at 19:33
apologies, table structure added. – Dino Feb 21 at 19:35
i think you missed date – Dagon Feb 21 at 19:39
Hi, i didn't I intentionally missed it. I do not want to store date. – Dino Feb 21 at 20:35

2 Answers

up vote 0 down vote accepted

If you are going to have a similar string with the same columns every time, then you could do something like this:

$n = explode("\n", $result);

// skip the first row since it's just column names
for ($i = 1; $i <= count($n); $i++)
{
  $cat = $n[$i][[0]; // category
  $date = $n[$i][[1]; // date
  $prod = $n[$i][[2]; // product
  $cost = $n[$i][[3]; // cost

  // and you can put it into a query like so
  $sql = "insert into myTable ('category', 'date', 'product', 'cost') values ('$cat', '$date', '$prod', '$cost')";
}

Does this answer your question?

share|improve this answer
1  
str_getcsv() as previously mentioned would be a better idea – Dagon Feb 21 at 19:40
Possibly so, I was just trying to offer a simple solution. – user1477388 Feb 21 at 19:40
1  
nice sql injection holes... – Marc B Feb 21 at 19:40
str_getcsv is simpler than your approach – Dagon Feb 21 at 19:41
Sorry, Marc, I was just showing the code for demonstrational purposes. – user1477388 Feb 21 at 19:41
show 3 more comments
up vote 0 down vote

Improving from previously answered post:

$n = explode("\n", $result);

// skip the first row since it's just column names

$sql = "insert into myTable ('category', 'date', 'product', 'cost') values ";

for ($i = 1; $i <= count($n); $i++)
{
  $cat = $n[$i][[0]; // category
  $date = $n[$i][[1]; // date
  $prod = $n[$i][[2]; // product
  $cost = $n[$i][[3]; // cost

  $sql .= "('$cat', '$date', '$prod', '$cost'),";
}

$sql = trim($sql, ",");

if(count($n) != 0)
    mysql_qiery($sql)

This gives you just one single query which will insert multiple rows.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.