...

View Full Version : Adding array items to database



cxn
08-25-2009, 02:07 AM
Hi all

I'm pretty new to php/mysql, which causes me to get stuck .. everywhere.

I have a form to input a string. I used the explode function to cut the string in parts (array items). Each item is a username from a site I'm active on.

My database table has 2 columns, "username" and "last checked".

What I want is that each username gets put in a new row of the table.

I'm assuming a foreach loop around the array would do the trick, but I have no success this far. I've been googling a lot on how to put an array into 1 column of a database, but found practically nothing that helped.

Thanks for your time.

mlseim
08-25-2009, 05:20 AM
Show us the PHP script you have so far ... with the explode stuff and
what you do with connecting to MySQL (but delete your password so we can't see it).

I'm also guessing that each username gets a new row, but not if the name
already exists? In that case, the username is not given a new row?

So there's some decisions to make along with populating your table.

We're interested to see what you've already done.

prasanthmj
08-25-2009, 06:39 AM
First you need to learn
* Database basics
* SQL
* PHP

Then go through some tutorials:
registration form using PHP (http://www.html-form-guide.com/php-form/php-registration-form.html)

login form using PHP (http://www.html-form-guide.com/php-form/php-login-form.html)

cxn
08-25-2009, 02:09 PM
Show us the PHP script you have so far ... with the explode stuff and
what you do with connecting to MySQL (but delete your password so we can't see it).

I'm also guessing that each username gets a new row, but not if the name
already exists? In that case, the username is not given a new row?

So there's some decisions to make along with populating your table.

We're interested to see what you've already done.

Hi, here's the code;
$inputarray = explode(" - ", $inputcut);

$inputarray = array_diff($inputarray, array(array_pop($inputarray)));


foreach ($inputarray as $value) {
echo $value . "<br />"; }


$con = mysql_connect('localhost','user','password')
or die('Connecting to database failed.');
mysql_select_db('cxn_project',$con);


$data = '';
foreach ($inputarray as $value) {
$data .= "('$value'),";
}
$data = rtrim($data, ',');
if (mysql_query("INSERT INTO online (Username) VALUES $data")) {
echo "Data inserted!";
}



$q2 = mysql_query($query);

if (!$q2)
{
echo ('Q2 failed: ' . mysql_error());
}
else {echo 'success Q2';}


I got it to work! Query works and it get put in my table :-)

I doubt this is the most efficient way to code it. Though currently it works.

You're right that I want to overwrite existing username rows, but new ones should be added. That's wherer I'm stuck now :(

cxn
08-25-2009, 05:05 PM
I got it to work! Query works and it get put in my table :-)

I doubt this is the most efficient way to code it. Though currently it works.

You're right that I want to overwrite existing username rows, but new ones should be added. That's wherer I'm stuck now :(



I guess I can use the UPDATE command? Though I think I need to loop through the rows first.. or not?

cxn
08-26-2009, 12:07 AM
Is what I want even possible? Thanks

takenreality
08-26-2009, 12:40 AM
if you use the UPDATE statement in your SQL query the database will not add a new row, IE your table will not grow. UPDATE will only update a existing database row and has a different query structure.

exactly what kind of input are you putting into the database? do you want every word to be a new line (row) of the database or do you want the database to take the input as an entire row? I can't wrap my head around why you would break up the input. :confused:

you can use GETDATE() to plug the current timestamp into your database then use date() to form it in anyway you see fit.

tailender1
08-26-2009, 07:20 AM
try doing this



if(!mysql_query($UPDATE_sql_statement)){
mysql_query($INSERT_sql_statement);
}

cxn
08-26-2009, 08:35 AM
if you use the UPDATE statement in your SQL query the database will not add a new row, IE your table will not grow. UPDATE will only update a existing database row and has a different query structure.

exactly what kind of input are you putting into the database? do you want every word to be a new line (row) of the database or do you want the database to take the input as an entire row? I can't wrap my head around why you would break up the input. :confused:

you can use GETDATE() to plug the current timestamp into your database then use date() to form it in anyway you see fit.


I got a string of users on line seperated by " - " (space, -, space). Eg Person1 - Person2 - Persosn3 ... That's why I want to seperate them. Each new db line has 1. the username, 2. the date I submitted them into the db.

Thanks for the help, I'll be looking into it tonight and post my progress :-)


@Tailer, thanks, seems so simple :-)

cxn
08-26-2009, 05:09 PM
try doing this



if(!mysql_query($UPDATE_sql_statement)){
mysql_query($INSERT_sql_statement);
}


Hmm, do I have to insert a loop somewhere as well?

tailender1
08-26-2009, 09:43 PM
Hmm, do I have to insert a loop somewhere as well?
well the logic is to see whether update query is a success or not and if it is not successful then try to insert ..
i think this is sufficient but there is no error handling implemented which you have to do it yourself...

Phil Jackson
08-28-2009, 03:34 PM
<?php

$inputarray = explode(" - ", $inputcut);
$inputarray = array_diff($inputarray, array(array_pop($inputarray)));

foreach ($inputarray as $value)
{
echo $value . "<br />"; }

$con = mysql_connect('localhost','user','password') or die('Connecting to server failed.');
mysql_select_db('cxn_project',$con) or die('Connecting to database failed.');

$data = '';
foreach ($inputarray as $value)
{
$data .= $value;
}

$data = rtrim($data, ',');
$query = mysql_query("SELECT * FROM `online` WHERE Username = '$data'");
if(mysql_num_rows($query)!=0)
{
echo "User: ".$data." already exisits";
}
else
{
if (mysql_query("INSERT INTO `online` (Username) VALUES ('$data')"))
{
echo "Data inserted!";
}

$q2 = mysql_query($query);
if (!$q2)
{
echo ('Q2 failed: ' . mysql_error());
}
else
{
echo 'success Q2';}
}
}
}
?>

cxn
08-28-2009, 04:17 PM
<?php

$inputarray = explode(" - ", $inputcut);
$inputarray = array_diff($inputarray, array(array_pop($inputarray)));

foreach ($inputarray as $value)
{
echo $value . "<br />"; }

$con = mysql_connect('localhost','user','password') or die('Connecting to server failed.');
mysql_select_db('cxn_project',$con) or die('Connecting to database failed.');

$data = '';
foreach ($inputarray as $value)
{
$data .= $value;
}

$data = rtrim($data, ',');
$query = mysql_query("SELECT * FROM `online` WHERE Username = '$data'");
if(mysql_num_rows($query)!=0)
{
echo "User: ".$data." already exisits";
}
else
{
if (mysql_query("INSERT INTO `online` (Username) VALUES ('$data')"))
{
echo "Data inserted!";
}

$q2 = mysql_query($query);
if (!$q2)
{
echo ('Q2 failed: ' . mysql_error());
}
else
{
echo 'success Q2';}
}
}
}
?>



Hi, thanks for the reply. I pretty much understand the coding, but it doesn't work unfortunately :(


Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/cxn/public_html/project/usersadded.php on line 42
Q2 failed: Query was emptyHeartBeat

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/cxn/public_html/project/usersadded.php on line 42
Q2 failed: Query was emptyMonoxideChild

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/cxn/public_html/project/usersadded.php on line 42
Q2 failed: Query was emptyNocturnal

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/cxn/public_html/project/usersadded.php on line 42
Q2 failed: Query was emptyAngelsReturn

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/cxn/public_html/project/usersadded.php on line 42
Q2 failed: Query was emptyCubest

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/cxn/public_html/project/usersadded.php on line 42
Q2 failed: Query was emptyGReddy


Those are the usernames I wanted to insert in my empty table.

I did alter your code a bit cause it gave an "unexpected" error.

This is what I have:


<html>
<style type="text/css">

body{
font-family: Arial, Verdana,Helvetica, sans-serif;

}

</style>
</html>


<?php

echo(date("l dS \of F Y h:i:s A") . "<br /><br />");

$input = $_POST['input'];

$inputcut = substr($input, 2);




$inputarray = explode(" - ", $inputcut);
$inputarray = array_diff($inputarray, array(array_pop($inputarray)));

foreach ($inputarray as $value)
{
echo $value . "<br />";

$con = mysql_connect('localhost',' useer ',' pw ') or die('Connecting to server failed.');
mysql_select_db('cxn_project',$con) or die('Connecting to database failed.');

$data = '';
foreach ($inputarray as $value)
{
$data .= $value;
}

$data = rtrim($data, ',');
$query = mysql_query("SELECT * FROM 'online' WHERE Username = '$data'");
if(mysql_num_rows($query)!=0)
{
echo "User: ".$data." already exisits";
}
else
{
if (mysql_query("INSERT INTO 'online' (Username) VALUES ('$data')"))
{
echo "Data inserted!";
}

$q2 = mysql_query($query);
if (!$q2)
{
echo ('Q2 failed: ' . mysql_error());
}
else
{
echo 'success Q2';
}
}
}
?>





Thanks for your help!

cxn
09-08-2009, 05:31 AM
bump :^$

Phil Jackson
09-08-2009, 12:50 PM
Can you give me an example of the input?

cxn
09-08-2009, 02:49 PM
Input:



- Person1 - Person2 - Person3 - 3 Users online

Phil Jackson
09-08-2009, 04:25 PM
Could you tell me why your using rtrim() ?



<?php
echo(date("l dS \of F Y h:i:s A") . "<br /><br />");
$input = "- Person1 - Person2 - Person3 - 3 Users online";
$inputcut = substr($input, 2);
$inputarray = explode(" - ", $inputcut);
$inputarray = array_diff($inputarray, array(array_pop($inputarray)));

foreach ($inputarray as $value)
{
echo $value . "<br />";

$con = mysql_connect('localhost',' useer ',' pw ') or die('Connecting to server failed.');
mysql_select_db('cxn_project',$con) or die('Connecting to database failed.');

$data = '';
foreach ($inputarray as $value)
{
$data .= $value;
}

$data = rtrim(trim($data), ',');
if($query = mysql_query("SELECT * FROM 'online' WHERE Username = '$data'"))
{
if(mysql_num_rows($query)!=0)
{
echo "User: ".$data." already exisits";

}
else
{
if (mysql_query("INSERT INTO 'online' (Username) VALUES ('$data')"))
{
echo "Data inserted!";
}
}

echo 'success Q2';
}
else
{
echo ('Q2 failed: ' . mysql_error() . "<br />Data: " . $data);
}
}
?>

cxn
09-08-2009, 07:11 PM
Could you tell me why your using rtrim() ?



<?php
echo(date("l dS \of F Y h:i:s A") . "<br /><br />");
$input = "- Person1 - Person2 - Person3 - 3 Users online";
$inputcut = substr($input, 2);
$inputarray = explode(" - ", $inputcut);
$inputarray = array_diff($inputarray, array(array_pop($inputarray)));

foreach ($inputarray as $value)
{
echo $value . "<br />";

$con = mysql_connect('localhost',' useer ',' pw ') or die('Connecting to server failed.');
mysql_select_db('cxn_project',$con) or die('Connecting to database failed.');

$data = '';
foreach ($inputarray as $value)
{
$data .= $value;
}

$data = rtrim(trim($data), ',');
if($query = mysql_query("SELECT * FROM 'online' WHERE Username = '$data'"))
{
if(mysql_num_rows($query)!=0)
{
echo "User: ".$data." already exisits";

}
else
{
if (mysql_query("INSERT INTO 'online' (Username) VALUES ('$data')"))
{
echo "Data inserted!";
}
}

echo 'success Q2';
}
else
{
echo ('Q2 failed: ' . mysql_error() . "<br />Data: " . $data);
}
}
?>


I was stuck with inserting array data, so I googled a lot and found a script with rtrim in it. It worked though.

Phil Jackson
09-08-2009, 07:52 PM
What is echoed out when the above script is ran?

cxn
09-08-2009, 08:03 PM
Tuesday 08th of September 2009 08:03:01 PM

Person1
Q2 failed: 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 ''online' WHERE Username = 'Person1Person2Person3'' at line 1
Data: Person1Person2Person3Person2
Q2 failed: 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 ''online' WHERE Username = 'Person1Person2Person3'' at line 1
Data: Person1Person2Person3Person3
Q2 failed: 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 ''online' WHERE Username = 'Person1Person2Person3'' at line 1
Data: Person1Person2Person3


Wow :/

Phil Jackson
09-08-2009, 08:06 PM
try



<?php
echo(date("l dS \of F Y h:i:s A") . "<br /><br />");
$input = "- Person1 - Person2 - Person3 - 3 Users online";
$inputcut = substr($input, 2);
$inputarray = explode(" - ", $inputcut);
$inputarray = array_diff($inputarray, array(array_pop($inputarray)));

foreach ($inputarray as $value)
{
echo $value . "<br />";

$con = mysql_connect('localhost',' useer ',' pw ') or die('Connecting to server failed.');
mysql_select_db('cxn_project',$con) or die('Connecting to database failed.');

$data = '';
foreach ($inputarray as $data)
{
if($query = mysql_query("SELECT * FROM `online` WHERE Username = '$data'"))
{
if(mysql_num_rows($query)!=0)
{
echo "User: ".$data." already exisits";

}
else
{
$data = mysql_real_escape_string($data);
if (mysql_query("INSERT INTO `online` (Username) VALUES ('$data')"))
{
echo "Data inserted!";
}
}

echo 'success Q2';
}
else
{
echo ("Q2 failed: (more than likely " . $data . "does not exist" . mysql_error() . "<br />Data: " . $data);
}
}
}
?>


THIS HAS JUST BEEN EDITED!!!

cxn
09-08-2009, 08:32 PM
try



<?php
echo(date("l dS \of F Y h:i:s A") . "<br /><br />");
$input = "- Person1 - Person2 - Person3 - 3 Users online";
$inputcut = substr($input, 2);
$inputarray = explode(" - ", $inputcut);
$inputarray = array_diff($inputarray, array(array_pop($inputarray)));

foreach ($inputarray as $value)
{
echo $value . "<br />";

$con = mysql_connect('localhost',' useer ',' pw ') or die('Connecting to server failed.');
mysql_select_db('cxn_project',$con) or die('Connecting to database failed.');

$data = '';
foreach ($inputarray as $data)
{
if($query = mysql_query("SELECT * FROM `online` WHERE Username = '$data'"))
{
if(mysql_num_rows($query)!=0)
{
echo "User: ".$data." already exisits";

}
else
{
$data = mysql_real_escape_string($data);
if (mysql_query("INSERT INTO `online` (Username) VALUES ('$data')"))
{
echo "Data inserted!";
}
}

echo 'success Q2';
}
else
{
echo ("Q2 failed: (more than likely " . $data . "does not exist" . mysql_error() . "<br />Data: " . $data);
}
}
}
?>


THIS HAS JUST BEEN EDITED!!!


It echoes


Tuesday 08th of September 2009 08:31:09 PM

Person1
Data inserted!success Q2Data inserted!success Q2Data inserted!success Q2Person2
User: Person1 already exisitssuccess Q2User: Person2 already exisitssuccess Q2User: Person3 already exisitssuccess Q2Person3
User: Person1 already exisitssuccess Q2User: Person2 already exisitssuccess Q2User: Person3 already exisitssuccess Q2


And my Table is:


Rows Username
1 Person1
1 Person2
1 Person3

Starting to work!

Phil Jackson
09-08-2009, 08:40 PM
<?php
echo(date("l dS \of F Y h:i:s A") . "<br /><br />");
$input = "- Person1 - Person2 - Person3 - 3 Users online";
$inputcut = substr($input, 2);
$inputarray = explode(" - ", $inputcut);
$inputarray = array_diff($inputarray, array(array_pop($inputarray)));

foreach ($inputarray as $value)
{
$value = trim($value);
echo $value . "<br />";

$con = mysql_connect('localhost',' useer ',' pw ') or die('Connecting to server failed.');
mysql_select_db('cxn_project',$con) or die('Connecting to database failed.');

if($query = mysql_query("SELECT * FROM `online` WHERE Username = '$value'"))
{
if(mysql_num_rows($query)!=0)
{
echo "User: ".$value." already exisits<br />";

}
else
{
$data = mysql_real_escape_string($data);
if (mysql_query("INSERT INTO `online` (Username) VALUES ('$value')"))
{
echo "Data inserted!<br />";
}
}
}
else
{
echo ("Q2 failed: (more than likely " . $value . "does not exist" . mysql_error() . "<br />Data: " . $value . "<br /");
}
}
?>

cxn
09-08-2009, 08:51 PM
<?php
echo(date("l dS \of F Y h:i:s A") . "<br /><br />");
$input = "- Person1 - Person2 - Person3 - 3 Users online";
$inputcut = substr($input, 2);
$inputarray = explode(" - ", $inputcut);
$inputarray = array_diff($inputarray, array(array_pop($inputarray)));

foreach ($inputarray as $value)
{
$value = trim($value);
echo $value . "<br />";

$con = mysql_connect('localhost',' useer ',' pw ') or die('Connecting to server failed.');
mysql_select_db('cxn_project',$con) or die('Connecting to database failed.');

if($query = mysql_query("SELECT * FROM `online` WHERE Username = '$value'"))
{
if(mysql_num_rows($query)!=0)
{
echo "User: ".$value." already exisits<br />";

}
else
{
$data = mysql_real_escape_string($data);
if (mysql_query("INSERT INTO `online` (Username) VALUES ('$value')"))
{
echo "Data inserted!<br />";
}
}
}
else
{
echo ("Q2 failed: (more than likely " . $value . "does not exist" . mysql_error() . "<br />Data: " . $value);
}
}
?>


Wow, I think that worked perfectly.
Just edited the input array to the variable input box I had, and it works like a charm :-)
Thanks a lot.

Do you have any guidelines on how I should add the following?
1. another column with "last updated" -> a data
2. some sort of box next to each username (in my output, which I still have to code), so the username will marked red or something.

Phil Jackson
09-08-2009, 08:59 PM
im a bit pushed for time at the min if no one else has sorted you out i'll do in the morning. Glad to here things working ok.

cxn
09-08-2009, 09:00 PM
I'm gonna do some research and try a bit myself :-)
Thank you so much for your time!

Phil Jackson
09-08-2009, 09:01 PM
Last updated:
(you need to make the row in your DB, 'lastUpdated')



<?php
echo(date("l dS \of F Y h:i:s A") . "<br /><br />");
$input = "- Person1 - Person2 - Person3 - 3 Users online";
$inputcut = substr($input, 2);
$inputarray = explode(" - ", $inputcut);
$inputarray = array_diff($inputarray, array(array_pop($inputarray)));

foreach ($inputarray as $value)
{
$value = trim($value);
echo $value . "<br />";

$con = mysql_connect('localhost',' useer ',' pw ') or die('Connecting to server failed.');
mysql_select_db('cxn_project',$con) or die('Connecting to database failed.');

if($query = mysql_query("SELECT * FROM `online` WHERE Username = '$value'"))
{
if(mysql_num_rows($query)!=0)
{
echo "User: ".$value." already exisits<br />";

}
else
{
$data = mysql_real_escape_string($data);
$lastUpdated = mysql_real_escape_string(date("l dS \of F Y h:i:s A"));
if (mysql_query("INSERT INTO `online` (Username, lastUpdated) VALUES ('$value', '$lastUpdated')"))
{
echo "Data inserted!<br />";
}
}
}
else
{
echo ("Q2 failed: (more than likely " . $value . "does not exist" . mysql_error() . "<br />Data: " . $value . "<br /");
}
}
?>




$lastUpdated = mysql_real_escape_string(date("l dS \of F Y h:i:s A"));
if (mysql_query("INSERT INTO `online` (Username, lastUpdated) VALUES ('$value', '$lastUpdated')"))

cxn
09-09-2009, 12:16 AM
Thanks for the coding yet again.

Though it doesn't give an error, the column "lastupdated" is empty.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum