How do I create a MySQL database & create simple Queries?

If you don't have PHPMyAdmin, install it on your system.  It is a web-based tool that allows you to create MySQL databases. It will also show you the syntax used to create the databases and tables as you create them.  It also has a SQL "tab/window" that will allow you to execute queries on your tables.

Hello there,

 I have PHPMyAdmin on my local WAMP server.

Please can I just re-iterate - I know NOTHING about how to "create queries", execute them, create tables, etc - I am a total beginner at this - I have dropped the info we were given into PHPMyAdmin on my local WAMP server, but thats about it,  I dont understand the terminolgy used, & the languge used in the strings or queries is gobbledegook to me,

I need someone to really hold my hand in going through this step by step, :)

...this is all I have on WAMP :) - many thanks.

Hi,

This site will help you get through the basics of creating a database as well as explaining queries.
It is quite good.

http://www.w3schools.com/sql/sql_intro.asp

Thanks Sindread - I had tried reading through some it it but got a bit lost & panicked.  Will try it again,

scotia

on the tab labeled SQL you will be able to formulate/type queries and execute them. Ex:

SELECT * FROM buy

will give you the contents of the buy table.

If you are not familiar at all with sql, I suggest you go through some tutorials -ex:
http://w3schools.com/sql/default.asp

Then follow that with php+mysql tutorial:
http://www.freewebmasterhelp.com/tutorials/phpmysql/4

...thanks guys - Im going through the w3c ones, but - it talks about syntax & statements etc - but I dont understand how to set up both my database & the webpages to display the outcome of the stamtement?

I need to understand how to link my website buttons or text links & provide the extracted data in tables embedded in the webpages?  Like the example link I provided in my first post - this database appears to sit on the website, with its contents updatable - how do I acheive this linked functionality?

scotia

...ok Im just not understanding;

heres my website where I need to show the results of any "trades"  - I need to learn how to link these pages & create functionality - ie, via the text links, to the database Im going to start re-building via PHPMyAdmin on the same server.  I dont see anything about this on the w3c tutorials, Im just not understanding what I need to do.  I need a database, a PHP file (to extract data from/parse ?) & a webpage to diaply the results - thats about all I understanbd LOL.

http://www.palegallery.com/uniwork/myTrader

>>...thanks guys - Im going through the w3c ones, but - it talks about syntax & statements etc -
The Database "holds" the information. You need to know the syntax to extract the information. Hence the reason for the first link.

>>but I dont understand how to set up both my database & the webpages to display the outcome of the stamtement?
On the image you attached, I can clearly see that you already have data in the db. So the database if already setup. What you need now is to create a php page that will connect  to the db AND execute an sql statement to get you the data that you want/need. That is what the SECOND link I gave you is about.

A basic php script to retrieve the content of the buy table is below.

Also, be sure to read through the mysql section on the PHP.net site -ex:
http://us.php.net/manual/en/function.mysql-query.php

If you are not familiar with a php function, you can find it there.  Be sure to read through the user-contributed notes as well.

Regards,
Hielo

<?php
$server="localhost";
$username="PUT_YOUR_DB_USERNAME_HERE";
$password="PUT_YOUR_DB_PASSWORD_HERE";
$database="mytrader";
mysql_connect($server,$username,$password) or die( mysql_error() );
mysql_select_db($database) or die(mysql_error() );
$query="SELECT * FROM `buy`";

$result=mysql_query($query) or die( mysql_error() );
if( 0==mysql_num_rows($result) )
{
  echo 'There is no data in buy table.';
}
else
{
  echo '<table>';
  while( $row=mysql_fetch_assoc($result)){
    echo '<tr><td>'. implode('</td<td>', $row) . '</td></tr>';
  }
  echo '</table>';
}
mysql_free_result($result);
mysql_close();
?>

Select allOpen in new window

...sorry - I didnt mean to sound rude in any sense, just that it can be hard to make sense of tutorials when you dont understand the genre of langauge your reading & the frustration creeps in.  The w3c one doesnt explain anything about connectivity so Ill try the second one you suggested, thanks.

The info in the database we were given as a txt file, all I had to do was click to create the database, then import - its was logical, not known - if that makes sense & apart from creating a simple site thats as far as Ive got.

Ill go & read the second tutorials - thanks for your time for now,
scotia  

...In fairness, only after having completed the first part of this module at uni was I told that for the second part (this part) I should have had 2yrs working knowledge of databases - so in that respect - yes, Im trying to grasp a lot in a short time - I simply want to scrape the 40% mark to move onto third year, in order to return to my common subjects - no pass, no third year & having self funded as a single mum the past two years, Im keen to scrape LOL.

Im ok with CSS, I use PHP to parse email forms in flash, & my HTML is workable.  Im going to follow your work above, (thank you very much) & let you know how I get on,

cheers, scotia

Thank you, yes that works for me - my webserver automatically prefixs' user names which I didnt realise until now, but I at least understand how to "talk to it"- LOL.

So, basically I could replace my traditional index.html with (for example) trader.php & use php to talk to the server, & formatting via css & html?

scotia

>>So, basically I could replace my traditional index.html with (for example) trader.php & use php to talk to the server, & formatting via css & html?

Bingo!

..lol, am still struggling - so will come back & pick your brains again soon - my parents neighbour is a programmer so Im hoping for some hands on tutoring to give me a better idea of the basics :)

scotia

...nope - Im still getting no-where apart from to the verge of tears.  

Ive set up index.php with one "link" to glass.php where I want to display all regions.  I think my "link" in my index.php is wrong & in addition even going through four different tutorials (my error messages vary) I can either,

display all the regions in one long row, each with the word region in front of it, or I can get one region (the first one in the database table).  Ive tried to format the output using various scraps from the tutoruals, but to no use.

I really need some solid, step by step help - please assume I know nothing (which is true) & help me achieve my final required goals.  I cant cope with this much longer.

what Ive got so far

<?php

if( isset($_GET['id']) && !empty($_GET['id']) )

{

include("dbstuff.php");

mysql_connect($server,$username,$password) or die( mysql_error() );

mysql_select_db($database) or die(mysql_error() );

$id=mysql_real_escape_string($_GET['id']);

$query="SELECT * FROM `buy` WHERE region= " . $id;

$result=mysql_query($query) or die( mysql_error() );

if( 0==mysql_num_rows($result) )

{

echo 'There is no data in table.';

}

else

{

echo '<table>';

while( $row = mysql_fetch_array( $result )){

echo $row['region'];

}

echo '</table>';

}

mysql_free_result($result);

mysql_close();

}

?>

Select allOpen in new window

The above gives me one long line of code, but when Im trying to add in the echos for tr & td only pulls the first region?

Many thanks experts,
scotia

...sorry -

The first thing I want to achieve is to show all records, all data etc on the index.php page, formatted into a neat table.  Ive got my css for tables set up in my css file, is this correct?

Second thing is to create an ahref which will bascially only show the records from a particular region.

Many thanks, I should have been clearer!

>>The above gives me one long line of code, but when Im trying to add in the echos for tr & td only pulls the first region?
use  mysql_fetch_assoc instead of  mysql_fetch_array:
...
while( $row = mysql_fetch_assoc( $result )){

echo '<tr><td>', $row['region'] , '</td></tr>'.PHP_EOL;

}
...

Additionally, if you clicked on a link that is SIMILAR to the following:
yourPage.php?id=3

and there is only one item with id=3, then all you will see is ONE <TR>

Hello again, Im sorry to be such a numpty.  

I had changed my "link" from the orginal one you sent (since I worked out if nameing an id=3 it would only pull that one record) so will go & try your suggestion - I really appreciate your help.

scotia

...do I save the doctype html into a seperate php file?

>>...do I save the doctype html into a seperate php file?
No. That is all meant for ONE file.

..ok, does the doctype still go at the top of the file or not?...sorry

>>..ok, does the doctype still go at the top of the file or not?...sorry
COPY and PASTE what I provided in the attached code of  post ID:35827398 EXACTLY as is.

hielo: please dont think Im not grateful for your help - but Ive mentioned several tines how hard I find this - & I promise I am trying to make sense & implement what you have obviously taken a great deal of time to explain to me - but - if I dont understand something, I need to ask additional questions, I hope thats ok.  Obviously I pay a subscription here & I dont want to waste your time, or mine with questions you perhaps find irritating.

Its 23.20pm here & Ive been trying to work on this since 8am, so it shows you just how stupid I am *smiles*.  Ill leave it herre tonight & will implement your suggestion tomorrow.

Are you able to address my requirements posted in  ID: 35827172 - I appreciate Im trying to run before I can walk, but this is incredibly important to me.  If I fail I have to leave uni & Ive worked 38 years to finally get there.

Many thanks as ever, scotia

>> Obviously I pay a subscription here
I don't understand what's your point.  I don't get paid.  I am helping you out of my own free will. If you were in some other forum (free or not) and I saw your post, I would help you just the same.

>>Are you able to address my requirements posted in  ID: 35827172
That's exactly what that post is supposed to do

Im not suggesting you get paid - Im suggesting I find some of what you are posting difficult to understand  & since you dont get paid - but I do pay to be here, I dont want to waste your time (or mine) by my being unclear.  

I need someone paid or otherwise to give me a really step by step guide on how to do this because I dont understand.  I subscribed to this forum because thats what I thought I would be able to ask for, Im sorry if thats wrong.  I didnt understand that the last section of code was simply to be copied & pasted into one file & you didnt explain that - I  really am at that basic a level.  

I have gone through the tutorials you suggested & a few more besides as well as trying to follow & implement all you have suggested - but Im getting no further forward & time is ticking on.

You posted I should save the code as helio.php but didnt mention why, or what it wass supposed to do - how to link it in with the other stuff youve kindly posted, or where to save it once completed?

I dont understand this "Additionally, if you clicked on a link that is SIMILAR to the following:
yourPage.php?id=3

and there is only one item with id=3, then all you will see is ONE <TR> "  - I explained Id tried to change your one id example to something more suitable for my requirements with no luck, just feel your getting p*ssed off at me for being so basic.

...Ok, I saved the above & popped it onto the server & called it directly -

http://www.palegallery.com/myTrader/helio.php

 I can see it lists everything from the database, but I dont understand the duplicate column numbers, & when I click on a linked I get the following error - (which I had prevously when I was trying to kplay with something in a tutorial too) "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 'West' at line 1" - is it just due to the single v double quotes perhaps?

I appreciate what you've given me, but I dont know how to proceed with it further for what I need?
Many thanks, scotia

>>You posted I should save the code as helio.php
My handle is Hielo

Not helio :)

..jings am really sorry hielo - Im dyslexic :)  Many thanks for the explanations too & Ill update my info as suggested in addition to implementing your codes, many thanks :)

Hello folks - Im sorry that it appeared as though my question had been abandoned, Ive been in hospital, but still require help with this, I have restarted everything, & now have the following code which doesnt throw any errors, but it doesnt display any data in the table either, I hope the code is ok format wise,

I simply want a user to click a link which will then show on the query.php page all the "paper trade" ordered by price.  Nothing displays so I am obviously still doing something wrong, please please please can someone hep me to do this?  I have gone through so many tutorials, videos etc & I just need to get this ONE query to work so that I can at least have learnt something.  

I have replaced my username & password & although I undersand how to use the "include" for this info Ive left it as is for now,

Many thanks,
scotia

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/>
<meta http-equiv="Content-Language" content="en" />
<meta name="description" content="myTrader Website" />
<meta name="keywords" content="myTrader Website" />
<title>myTrader Website</title>
<link href="trader.css" rel="stylesheet" type="text/css">
</head>

<!--main nav & layout do not alter-->
<div class="container">
<div id="navigation">

<img src="images/miniLogo.jpg" border="0">

<P>
<ul class="arrow">
<li><a href="index.php"><span class="paraHeadingOne">Trading Home</span></a></li>
<li><a href="query.php?commodity=Paper"><span class="paraHeadingOne">All paper trade</span></a></li>
<li><a href="query.php"><span class="paraHeadingOne">Open Trade Bid</span></a></li>
<li><a href="query.php"><span class="paraHeadingOne">View Recent Trades</span></a></li>
</ul>
</div></div>

<!-- end of nav area-->

<div id="centerDoc">
<table id="mainTable">
<tr><td width="300" valign="top"><H1>Welcome to myTrader!</H1>
<span class="mainText">Glass & Paper Recycled Commodities Trading Site</span></td>
<td valign="top" align="right"><img src="images/largeLogo.jpg" width="406" height="113" border="0"></td></tr></table>
<P align="left">

	<table>
	<tr>
	<th>Commodity</th><th>Region</th><th>Member</th><th>Size</th><th>Price</th><th>Date</th>
	</tr>

<?php
$link = mysql_connect('localhost', 'user', 'pass');
if (!$link) {
    die('Could not connect: ' . mysql_error());	
}
echo 'connected ok';

$query = mysql_query ("SELECT * FROM sell WHERE commodity='Paper' ORDER BY 'price'");


echo "<tr><td>".$row['commodity']."</td>
<td>".$row['region']."</td>
<td>".$row['member']."</td>
<td>".$row['size']."</td>
<td>".$row['price']."</td>
<td>".$row['posted']."</td></tr>";


mysql_close($link);
?>
</table></P>

</td></tr></table>
</body></html>

Select allOpen in new window

Hello hielo, thank you - Im am indeed a UK keyboard so will go & change this throght my files & get back to you,

Hope you are well.

Yay! I found the ` - it lives beside the ¬ (at least Ive managed something!)

..so, I gave it a bash, but it doesnt seem to make any difference, I still cant get any information to display in my table so there are obviously many other things I am still doing wrong!  I do appreciate how difficult it is without seeing how the database is set up, we werre told how to import the data we were given & thats all I can work with even though it doesnt seem logically set up.

Dont know what else to do.  I have to pick up my dad from hospital so will have a think - many thanks.

...thank you  - again I dont see anything on the page, Im sorry!  Is there no way you could look at my database & see if there is something wrong there?  It has to be set up the way it is, but I could easily have made a mistake somewhere?

Many thanks,
scotia

Hello there, thank you for all your advice & support - Ive manged to now get the page to connect to the database & it displays one row of information correctly, but I wondered why not the rest of it?

Many thanks, scotia

<?php
$link = mysql_connect('localhost', 'user', 'pass');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
echo 'newTrader connected ok';
$db_selected = mysql_select_db('palegall_newTrader', $link);
if (!$db_selected) {
    die ('cant find newTrader' . mysql_error());
}


$query = mysql_query ("SELECT * FROM `sell` WHERE `commodity`='Paper' ORDER BY `price`") or die( mysql_error() );
$row=mysql_fetch_assoc($query);

echo "<tr><td>".$row['commodity']."</td>
<td>".$row['region']."</td>
<td>".$row['member']."</td>
<td>".$row['size']."</td>
<td>".$row['price']."</td>
<td>".$row['posted']."</td></tr>";



mysql_close($link);

?>
</table></P>

Select allOpen in new window

Hiya :)

I tried the above, but get an error message;

newTrader connected ok
Warning: Wrong parameter count for mysql_num_rows() in /home/palegall/public_html/Reassessment/query.php on line 50

(line 50 in my file is>>>>>            if( mysql_num_rows() < 1 )

Many thanks,

...sorry ignore that, I made an error - my apolgies :(

Nope, I checked again & Im still getting the same error - sorry to be a pest :(

yaaaaaaaaaaaaay! - stuff showed up!  I removed the if & else, & all the data now shows!!! I know its probably not correct to miss it out, but I just wanted to see if it would work.

Ok, so now I need to get the `buy` table to show too, & hopefully learn how to "join" those & create filtering - thank you so much for all your efforts so far - I know Ive got such a long way to go but this is the first time Ive seen the data appear - vewrry exciting for me! lol...

....bck soon, scotia

Thank you again :)
This is where Im at now, myTrader

I would like to know it its possible to filter my results by region & what would be the best way to do this? I looked at the w3c tutorial for filtering using a dropdown box & implementing a script but wondered if its possible to do this by the region heading?

Many thanks

sorry - this is the correct link

:(

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.