# Solved: MySql and phpMyAdmin query



## JiminSA (Dec 15, 2011)

Having set up a database and table using phpMyAdmin (which is the 3rd party software used by my main website host) I am wondering what to do next?

I managed to get hold of the SQL script that created the table - must I run it on my website using a php page?

Will the database be visible in FileZilla or is it hidden away somewhere?

Thanks in anticipation...


----------



## JiminSA (Dec 15, 2011)

I must add that I have already attempted to insert data into the db table I set up in phpMyAdmin, with no success... I am missing a step here aren't I?


----------



## allnodcoms (Jun 30, 2007)

Right, here goes...

A few security tips:

Create a new user (or two!). Never, ever (ever, ever!) connect to your DB as root. Create a user who can read data, and another who can read / write, and use the appropriate user for the job.
Write your DB connect function in a separate page and bury it deep! Don't put you 'mysql_connect()' function in a top level page, put a custom function (or two) in a separate page and 'require' it. Bury this page a few levels down in your hierarchy and CHMOD these directories to the hilt.
Add an 'index.php' page to each directory. Each of the directories talked about above should have an index.php fie in them that simply redirects to your top level index page. In this way, if someone tries to list your files (by just typing the directory name) they will be bumped to your home page.
To actually 'use' your database:

Connect to the server with mysql_connect();
Select the database to use on that server with mysql_select_db();
Construct the query string and pass to mysql_query();
 ~ Handle the query result. Depends on specific circumstances ~
 Close the connection to the server with mysql_close();
Hope that clears it up a little bit!

Danny


----------



## JiminSA (Dec 15, 2011)

Hey thanks Danny! Those security tips are a great help!

I have a problem inserting data from the old serialised file into the db table (vehicle) which I constructed using phpMyAdmin. As you'll see from the bit of code below I have followed the access protocol, but no data gets transferred - I get the following warning


> Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in /home/surfwhee/public_html/CMS/convert.php on line 43


here's the code...

```
$count = 0;

$vehicles = LoadVehicles();

$con = mysql_connect("localhost","surfwhee_jim","********************");

if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("surfwhee_vehicles", $con);

foreach ($vehicles["vehicles"] as $id => $vehicles)
{
	$count += 1;
    echo date("M j, Y, g:i a", $vehicles['datetime']) .  " Count: " . $count . "
\n";
	mysql_query("INSERT INTO vehicle 
	(datetime, fullname, email, phone, cell, suburb, make, model, year, kms, colour, extras, servhist, accidents, settlement, finhouse, monthsrem, monthsarr, amtrem, price, comments, tprice, rprice, natis, pics4, wcdisc, aprice, status, notes, agent, datetimestamp)
	VALUES 
	($vehicles[datetime], $vehicles[fame], $vehicles[email], $vehicles[phone], $vehicles[cell], $vehicles[suburb], $vehicles[make], $vehicles[model], $vehicles[year], $vehicles[kms], $vehicles[colour], $vehicles[extras], $vehicles[servhist], $vehicles[accidents], $vehicles[settlement], $vehicles[finhouse], $vehicles[monthsrem], $vehicles[monthsarr], $vehicles[amtrem], $vehicles[price], $vehicles[comments], $vehicles[tprice], $vehicles[rprice], $vehicles[natis], $vehicles[pics4], $vehicles[wcdisc], $vehicles[aprice], $vehicles[status], $vehicles[notes], 'Jim', date())");
}

$result = mysql_query("SELECT * FROM 'vehicle' ORDER BY 'datetime'");
if (mysql_num_rows($result)>0) {
    echo "DB table vehicle exists"; 			 //it exists
    } else {
    echo "DB table vehicle doesn't exist";       //it does not exist
    }
while($row = mysql_fetch_array($result))
  {
  echo $row['id'] . " " . date($row['datetime'],"M j, Y, g:i a") . " " . $row['fullname'] . "
";
  }

mysql_close($con);
```
line 43 being the mysql_num_rows statement

Any ideas? I get the feeling that there is something in the phpMyAdmin that I haven' done...


----------



## allnodcoms (Jun 30, 2007)

The mysql_query() function returns 'false' on error, so your SELECT query seems to be failing as the $result that you pass on to mysql_num_rows() is (according to your error) a boolean. The only reason I can think of that a standard select query would fail is insufficient access rights to the DB. Check your permissions in phpMyAdmin.

Another idea to aid debugging: after all your query calls, add 'or die(mysql_error());' - That should give you an idea of what's going on.

Danny


----------



## JiminSA (Dec 15, 2011)

Thank you Danny. 
I had some extraneous single quotes in the SELECT statement and after taking them out have gotten as far as the num_rows test - which proved negative, i.e. number of rows <= 0.

Well, I obviously have access permission to get that far, but what now? The table insert is obviously not grafting, but why not?

My code now looks like this...

```
$count = 0;

$vehicles = LoadVehicles();

$con = mysql_connect("localhost","surfwhee_jim","jamescyrilord2012") or die(mysql_error());

if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("surfwhee_vehicles", $con) or die(mysql_error());

foreach ($vehicles["vehicles"] as $id => $vehicles)
{
	$count += 1;
	echo date("M j, Y, g:i a", $vehicles['datetime']) .  " Count: " . $count . "
\n";
	mysql_query("INSERT INTO vehicle 
	(datetime, fullname, email, phone, cell, suburb, make, model, year, kms, colour, extras, servhist, accidents, settlement, finhouse, monthsrem, monthsarr, amtrem, price, comments, tprice, rprice, natis, pics4, wcdisc, aprice, status, notes, agent, datetimestamp)
	VALUES 
	($vehicles[datetime], $vehicles[fame], $vehicles[email], $vehicles[phone], $vehicles[cell], $vehicles[suburb], $vehicles[make], $vehicles[model], $vehicles[year], $vehicles[kms], $vehicles[colour], $vehicles[extras], $vehicles[servhist], $vehicles[accidents], $vehicles[settlement], $vehicles[finhouse], $vehicles[monthsrem], $vehicles[monthsarr], $vehicles[amtrem], $vehicles[price], $vehicles[comments], $vehicles[tprice], $vehicles[rprice], $vehicles[natis], $vehicles[pics4], $vehicles[wcdisc], $vehicles[aprice], $vehicles[status], $vehicles[notes], 'Jim', date())");
}

$result = mysql_query("SELECT * FROM vehicle ORDER BY datetime") or die(mysql_error());
if (mysql_num_rows($result)>0) {
    echo "DB table vehicle exists"; 			 //it exists
    } else {
    echo "DB table vehicle doesn't exist";       //it does not exist
    }
while($row = mysql_fetch_array($result))
  {
  echo $row['id'] . " " . date($row['datetime'],"M j, Y, g:i a") . " " . $row['fullname'] . "
";
  }

mysql_close($con) or die(mysql_error());
```


----------



## allnodcoms (Jun 30, 2007)

Believe it or not (and don't ask why, you don't want to know!) you need to put the PHP variables in single quotes, or escaped double quotes. Another example here: $vehicle[datetime] would become '$vehicle[datetime]' (and not $vehicle['datetime'] as you might think... and who said PHP was complicated?). This was my bad from earlier mate, sorry bloke!

Danny


----------



## JiminSA (Dec 15, 2011)

That's cool Danny, at least we're making progress!


----------



## JiminSA (Dec 15, 2011)

For some reason the foreach loop is not grafting! I just can't figure it out - here's the entire code including the unserialise... perhaps you can see the wood for the trees!

```
<?php
	function LoadVehicles()
	{
		$datevehiclesfile = "../vehicles1.dat";
		$result = @unserialize(@file_get_contents($vehiclesfile));
		if ($result === false)  $result = array("vehicles" => array(), "nextid" => 1);

		return $result;
	}

	function SaveVehicles($vehicles_out)
	{
		$vehiclesfile = "../vehicles1.dat";
		file_put_contents($vehiclesfile, serialize($vehicles_out));
	}

$count = 0;
$vehicles["vehicles"][$vehicles["nextid"]] = 
array(
"fame" => "",
"email" => "",
"phone" => "",
"cell" => "",
"suburb" => "",
"make" => "",
"model" => "",
"year" => "",
"kms" => "",
"colour" => "",
"extras" => "",
"servhist" => "",
"accidents" => "",
"settlement" => "",
"finhouse" => "",
"monthsrem" => "",
"monthsarr" => "",
"amtrem" => "",
"price" => "",
"comments" => "",
"photos" => "",
"tprice" => "",
"rprice" => "",
"natis" => "",
"pics4" => "",
"wcdisc" => "",
"aprice" => "",
"status" => "",
"notes" => "",
"datetime" => ""
);

$vehicles = LoadVehicles();
if (empty($vehicles))
	{
		echo "empty file vehicles1.dat";
		exit;
	}
$con = mysql_connect("localhost","surfwhee_jim","***************") or die(mysql_error());

if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("surfwhee_vehicles", $con) or die(mysql_error());

echo "begin the loop
";
foreach ($vehicles["vehicles"] as $id => $car)
{

echo "thru the loop
";
	$sval = array();
	$sval[0] = $car['datetime']; 
	$sval[1] = $car['fame']; 
	$sval[2] = $car['email']; 
	$sval[3] = $car['phone']; 
	$sval[4] = $car['cell']; 
	$sval[5] = $car['suburb']; 
	$sval[6] = $car['make']; 
	$sval[7] = $car['model']; 
	$sval[8] = $car['year']; 
	$sval[9] = $car['kms']; 
	$sval[10] = $car['colour']; 
	$sval[11] = $car['extras']; 
	$sval[12] = $car['servhist']; 
	$sval[13] = $car['accidents']; 
	$sval[14] = $car['settlement']; 
	$sval[15] = $car['finhouse']; 
	$sval[16] = $car['monthsrem']; 
	$sval[17] = $car['monthsarr']; 
	$sval[18] = $car['amtrem']; 
	$sval[19] = $car['price']; 
	$sval[20] = $car['comments']; 
	$sval[21] = $car['tprice']; 
	$sval[22] = $car['rprice']; 
	$sval[23] = $car['natis']; 
	$sval[24] = $car['pics4']; 
	$sval[25] = $car['wcdisc']; 
	$sval[26] = $car['aprice']; 
	$sval[27] = $car['status']; 
	$sval[28] = $car['notes']; 
	$sval[29] = 'Jim'; 
	$sval[30] = $_SERVER['REQUEST_TIME'];

	$count += 1;
	echo date("M j, Y, g:i a", $car['datetime']) .  " Count: " . $count . "
\n";
	mysql_query("INSERT INTO vehicle 
	(datetime, fullname, email, phone, cell, suburb, make, model, year, kms, colour, extras, servhist, accidents, settlement, finhouse, monthsrem, monthsarr, amtrem, price, comments, tprice, rprice, natis, pics4, wcdisc, aprice, status, notes, agent, date_time1)
	VALUES 
	('$sval[0]', '$sval[1]', '$sval[2]', '$sval[3]', '$sval[4]', '$sval[5]', '$sval[6]', '$sval[7]', '$sval[8]', '$sval[9]', '$sval[10]', '$sval[11]', '$sval[12]', '$sval[13]', '$sval[14]', '$sval[15]', '$sval[16]', '$sval[17]', '$sval[18]', '$sval[19]', '$sval[20]', '$sval[21]', '$sval[22]', '$sval[23]', '$sval[24]', '$sval[25]', '$sval[26]', '$sval[27]', '$sval[28]', '$sval[29]', '$sval[30]')")
	or die(mysql_error());
}

$result = mysql_query("SELECT * FROM vehicle ORDER BY datetime") or die(mysql_error());
if (mysql_num_rows($result)>0) {
    echo "DB table vehicle exists"; 			 //it exists
    } else {
    echo "DB table vehicle doesn't exist";       //it does not exist
    }
while($row = mysql_fetch_array($result))
  {
  echo $row['id'] . " " . date($row['datetime'],"M j, Y, g:i a") . " " . $row['fullname'] . "
";
  }

mysql_close($con) or die(mysql_error());

?>
```


----------



## JiminSA (Dec 15, 2011)

Sorry folks, I discovered why my foreach wasn't grafting! - during a search and replace I inadvertently changed the variable name in the Load function so that $datavehiclesfile became the recipient of the unserialised record and not $vehiclesfile as the code expects.

My MySql access problems are no longer... thanks Danny, for your input and patience! 

I am now experimenting with my new found toy - the best way to learn a subject I believe. 

Can you tell me Danny, is there a way either in php or in phpMyAdmin to reset the index auto_increment to start from 1 again after I have deleted the contents of a table I've been playing with?


----------



## allnodcoms (Jun 30, 2007)

There is Jim. Go into phpMyAdmin and select the database / table, then look at the tabs along the top, last one should be "options" [EDIT: Should be "Operations"]. Click there, second panel down on the left should be table options - auto inc is in there...

Danny


----------



## JiminSA (Dec 15, 2011)

Found it Danny... PACK_KEYS I assume from "default" to "1" ? Gonna try it and see...


----------



## allnodcoms (Jun 30, 2007)

JiminSA said:


> Found it Danny... PACK_KEYS I assume from "default" to "1" ? Gonna try it and see...


No, it's _AUTO_INCREMENT_ mate. You have to select a table that has an auto increment field, otherwise it doesn't show...

Danny


----------



## JiminSA (Dec 15, 2011)

Ooops! - I had turned off auto increment - it's now back on and I've set AUTO_INCREMENT to 1 - thanks...


----------



## JiminSA (Dec 15, 2011)

Totally impressed, Danny - MySql is very easy once one is over the initial learning curve.

Just a couple of drawbacks...
a) it doesn't handle a variable number of multiple elements (occurences) - no sweat, just optimise the number and create that many...
b) doesn't like single quotes in elements - also no sweat, just translate such elements with htmlspecialcharacters...

Otherwise it's cushty!


----------



## allnodcoms (Jun 30, 2007)

JiminSA said:


> a) it doesn't handle a variable number of multiple elements (occurences)


Not with you mate, what are you trying to do?

If you can give me an example I'm sure we can work it out...

Danny


----------



## JiminSA (Dec 15, 2011)

When a vehicle in the database has been edited, I would like it to reflect in the record - i.e. by which user and at what time - by including 2 fields "agent" and "date_time", one occurrence for each time it's edited - which can happen n times i.e. a variable number of occurrences in the life of the record.

If SQL can handle that scenario, I'd love to know...


----------



## allnodcoms (Jun 30, 2007)

It can James, welcome to the world of database design. 

I'd have a separate table called 'edits' or similar, this table has the name and timestamp, maybe a reason for the edit (?) and a reference to the main vehicle table that it refers to. Whenever the vehicle table is edited, you just create a new 'edits' entry and set the pointer to the vehicle entry's id.

Easy peasy!

Danny


----------



## JiminSA (Dec 15, 2011)

That can work! 
All I need to do is SELECT * FROM editing_table WHERE recid = $row['id'] ORDER BY datestamp
or summat like that...
Then there are no limits on the number of edit transactions! - I like the way you roll Danny!


----------



## allnodcoms (Jun 30, 2007)

It's easy when you know how mate... 

Danny


----------



## JiminSA (Dec 15, 2011)

Danny, is there anything to stop me serialising an array into a row and then un-serialising it?


----------



## allnodcoms (Jun 30, 2007)

Er... Why?

Danny


----------



## JiminSA (Dec 15, 2011)

That way I could have a variable number of occurrences of items. i.e. by putting arrays into cells, no?


----------



## allnodcoms (Jun 30, 2007)

No mate, you can't put arrays into a table. Basically a table is an array. If you want to embed an array into a table you would do as you did with the edited entries: create another table an link each instance back to the parent table with an ID field.

Danny


----------



## JiminSA (Dec 15, 2011)

Aha! On the premise that a serialised array is just a data string, I thought it would be possible to put it into a cell and guess what? You can! Check this
Is this a case of the student teaching the mentor? No offense mate


----------



## allnodcoms (Jun 30, 2007)

JiminSA said:


> Is this a case of the student teaching the mentor? No offense mate


Er... No.

_Technically_ the contents of a field is just a string of 1s and 0s, so you can (I repeat for emphasis - *technically*) put what you like in it. But to go back to my previous reply - Why? You lose the ability to query the data, you'd have to read it in, unpack it, look through it, pack it back up and shoe horn it back in there...

There are far more elegant ways of achieving the exact same thing without the use of power tools or large hammers 

Danny


----------



## JiminSA (Dec 15, 2011)

There is a valid reason why I would like this facility...

The quick 'n dirty CMS I'm using for this vehicle database originally used this technique (Serialize/Unserialize) for the whole file. I decided that because this db will get very big (4-5 records added daily, ad infinitum) that it was time to go MySql...
Now then, each vehicle entry can have any number of photographs uploaded and associated with it, id's of which are placed in an array within the serialised record.

The CMS photo handling (add/delete/re-order etc.,) is all oriented to an array set up and is fairly complex - hence the desire to have an array within a MySql cell.

Hope that clarifies it a bit, mate and I hope you don't think I was being arrogant in my last entry - I don't pretend that I could teach an old dog like you anything new!!!


----------

