Question about MySql, need help

Ok guys I have been working on my first database project. Just have a question. I have successfully created the tables and uploaded the contents from my forms into the tables and can view the contents in those tables successfully on the serverside.

I am having trouble finding what I need to do in order to display the contents of said tables in a webpage.

macro more!

What programming language can you use ?
Python, PhP, JAVA, Ruby, C#, any other thing ?

What data from the tables specifically are you trying to pull? You need to specify your query. For instance, trying to display username column from the users table,

Quote:

Pretty sure the poor guy is using php..

(Just to display the data in a table or so)
The basics that you are going to want to do is:
1. initialize a mysql connection
2. perform said query
3. Mock up html while looping thru the resultset
4. close connection

http://dev.mysql.com/tech-resources/articles/ddws/23.html

Now in order to make this editable it requires a little more.. You need to use some sort of id column to pass during your post in order to determine which row the user selected(also you will need to use an edit link that's embedded with an id in the querystring or some other form data embedded in your grid/table). You are better off finding a working example due to the amount of code this is going to require. In C# they have made this all much easier and not so rudimentary. The thing about programming is...there is always a hundred different ways to do something as well....

This.

MMMmmmm...

Visual Studios and C# & VB.net with a sql backend.

I can taste your tears.

I watched many a geek get destroyed by the year long project that built upon every weeks modules.

Ahhhh memories of CIS 321, 322, 323

Using C# / VB.NET backend isn't too terribly bad really. I mean, once the data connections are setup and you pull using cans it isn't horrible.

Hey guys its all been in PHP. As whole my db is solid and my upload is solid and running very smooth and the tables are all filling properly and recognizing primary keys etc.

Its basically a registration process for my college events. Now the problem I am having is that when I try to "dynamically" create a new webpage with the contents of the database is it only returning the first result in the database.

When I try to apply a loop I am not getting any of my debugging echos, not for my db connection or upload confirmation.

Feel free to take a look at the page, I dont host it, my annoying professor does.

http://ccis004.uncc.edu/~chaconia/final2.htm
http://ccis004.uncc.edu/~chaconia/home.php

I removed my attempt to recreate all the contents to the tables.
both uncc id and phone numbers are primary keys. Doesnt post anything now.

Post your source code ... specifically the query & function which gets the data & then attempts to loop through the rows.

That is a pretty straight forward exercise, whether it is in php, c#, vb or whatever.

You are either looping properly & only return a single row ... meaning your query is incorrect, or your loop is not moving through the records properly.

As Longshanks said above, you need to query the Db. This would be something along these lines:

SELECT ProductId, ProductName, ProductDescription
FROM Products

Use PHP to execute the query and you get a recordset, that is actually very much like the DB table, but only with the columns (ProductId, ProductName, ProductDescription) you specified in the SELECT clause.

When you have the records set, you loop through it. Sortof like this (i don't know PHP too well, so it will be pseudo code):

string query = "SELECT ProductId... " // see the query above
Recordset products = database.Execute(query)

write ("<table>")
foreach (product in products)
{

write ("<tr>")
write ("<td>" + product.ProductId + "</td>")
write ("<td>" + product.ProductName + "</td>")
write ("<td>" + product.ProductDescription + "</td>")
write ("</tr>")
}
write ("</table>")

This will display a html table with all the products in it. Change thje query (dynamically through PHP) to change what is displayed.

You probably know most of this, but I just wanted to give a comprihensive example (i hope).

Show me the you code, and I will be more specific ;-)

Hope this helps.

Post yur queries Ith.

Ok. So first thing, in PHP : use PDO. Nothing less.
Something like that :

Code:

<?php
// Change these vars with your server infos
$DB_HOST = 'yourDbHost';
$DB_NAME = 'yourDatabaseName';
$DB_USER = 'user';
$DB_PASS = 'pass';

try{
  // Instanciate a PDO object
  $db = new PDO('mysql:dbname='.$DB_NAME.';host='.$DB_HOST);
  // Get a PDO statement
  $stmt = $db->prepare('your SQL query here');
  // Fetch all results, may be bad if there's a shitload of data
  $data = $stmt->fetchAll();
}
catch(PDOException $e){
  echo 'PDO error';
  die();
}
catch(Exception $e){
  echo 'ouch';
  die(); // That's bad, I know
}
// You should write a proper exception handler which will log all the errors into a file
// We start outputting html here, you better put the html part in a different file which you'll include
?>
<table>
<?php foreach($data AS $tuple){ ?>
  <tr>
    <td><?=$tuple['column1']?></td>
    <!-- Add more column if needed -->
  </tr>
<?php }?>
<table>

As Im just starting this...what is a PDO.

The only query Im concerned with right now is
SELECT * FROM registree
Thats what is required, will add more queries as time permits. Most likely will just make another query on the primary keys.

Code:

<table>
<?php foreach($data AS $tuple){ ?>
  <tr>
    <td><?=$tuple['column1']?></td>
    <!-- Add more column if needed -->
  </tr>
<?php }?>
<table>

This is HTML output part and the creation of the tabular data on a new webpage correct?

PDO is a method to connect to databases with php. A good method, not like what you'll find in stupid tutorials.

So, you just copy-pasta your query where there's a "your query here" and the part you quoted is what will be output.
Depending on the number of fields you have in your table, just add more columns.
If you have fields like ID, name, desc you do that :

Code:

<table>
<?php foreach($data AS $tuple){ ?>
  <tr>
    <td><?=$tuple['ID']?></td>
    <td><?=$tuple['name']?></td>
    <td><?=$tuple['desc']?></td>
  </tr>
<?php }?>
<table>

Code:

<?php
mysql_connect("localhost", "database", "password") or die(mysql_error());
echo "Connected to MySQL<br />";
mysql_select_db("database") or die(mysql_error());
echo "Connected to Database<br />";


$result = mysql_query("SELECT * FROM registree") or die(mysql_error());  
$row = mysql_fetch_array( $result );

// Print out the contents of the entry 
echo "<table border='1'>";
echo "	<tr> 
	<th>Name</th> 
	<th>Address1</th> 
	<th>Address2</th> 
	<th>Email</th> 
	<th>Phone</th>
	<th>SpecAsc</th>
	<th>unccID</th>
	<th>Volunteer</th>
	<th>Session</th>
	</tr>";

while($row = mysql_fetch_array( $result )) 
{
echo " <tr><td> ";
	echo $row['name'];
echo " </td><td> ";
	echo $row['address1'];
echo " </td><td> ";
	echo $row['address2'];
echo " </td><td> ";
	echo $row['email'];
echo " </td><td> ";
	echo $row['phoneNo'];
echo " </td><td> ";
	echo $row['specAsc'];
echo " </td><td> ";
	echo $row['unccId'];
echo " </td><td> ";
	echo $row['volunteer'];
echo " </td><td> ";
	echo $row['session'];
echo " </td><tr> ";
}
echo "</table>";


?>

Well I just ended up using this and it seems to work fine. Ill look at PDO instancing at a later date. Ill go back and "variableize" it later. Ive been spending too much time on this as it is and my partner is clueless. So a two person job is now a me job.

And as annoying and outdated as Tutorials are. I have basically done this whole project from like 10 different tutorials. Since my professor is borderline terribad and the text is just front to back creating ER diagrams and Schemas.

So my next question would be, how could I look up the data by searching one particular field and still have it list all the data associated with it? Like have a search box for name, and pull up all the data with people that have that name

Just change you query by adding a where clause. Been a while since I did PHP .. but something like:

select * from registree where name = $_POST['NameOfYourTextbox'];

'NameOfYourTextbox' would be whatever you named the <input> html tag on your form.

A better practice would be to assign the posted value from the textbox to a local variable first, as such:

$myval = $_POST['NameOfYourTextbox'];
SELECT * FROM registree where name = $myval;

Other than grabbing the posted vaiable & adding it to a where clause, you should be able to use the rest of the code you posted to display the new filtered result set.

is it possible to add multiple options for doing searches.

Such as

Code:

$myval1 = $_POST['fname'];
$myval2 = $_POST['lname'];
$myval3 = $_POST['phoneNo'];
$myval4 = $_POST['email'];

SELECT * 
FROM registree
WHERE fname=$myval1 or lname=$myval2 or phoneNo=$myval3 or       email=$myval4;

where im posting the variables from a form, like a search box? like if i want to search all people with the first name ithkrul
or want to search all people with the last name katir. My concern is, can I use 'or' like this? and what are the annomalies possibly faced when searching like this?

or should i only concern myself with doing searches via primary key.

Also new question concerning secure logins.

ok so i have a log in via php, but if i just copy and paste the to the url it takes me to what should be password only? basically

http://ccis004.uncc.edu/~chaconia/admin.php

and

http://ccis004.uncc.edu/~chaconia/admin.php?password-blahblah&username=blahblah

take me to the same "admin" page regardless

You sure can do that ... you just just have to figure out your logical AND|OR's ... primary keys are typically hidden from the end user, you use these for performing updates or in JOINs from parent to child tables.

For the login ... you have to check for the password post form variable if it is not there ... meaning someone went directly to the process page ... you should redirect to an error page, or back to the login page & present the user with some message.

Use an if statement around your processing logic for the password lookup.

Everytime you use mysql_* php function or use raw form input data, baby raptor Jesus kills a puppy.

obviously for this type of assignment he is not wanting to safeguard against sql injection...

Generally for the search queuries..you can use something like this (I'm not a php guy..so no guarantees on syntax)

$myval1 = $_POST['fname'];
$myval2 = $_POST['lname'];
$myval3 = $_POST['phoneNo'];
$myval4 = $_POST['email'];

$myWhereMaker = " where 1=1 ";
if ($myval1 != "")
$myWhereMaker = $myWhereMaker + " and fname like %"+$myval1+"%";
if ($myval2 != "")
$myWhereMaker = $myWhereMaker + " and lname like %"+$myval2+"%";
if ($myval3 != "")
$myWhereMaker = $myWhereMaker + " and phoneno like %"+$myval3+"%";
if ($myval4 != "")
$myWhereMaker = $myWhereMaker + " and email like %"+$myval4+"%";

$result = mysql_query("SELECT * FROM registree" + $myWhereMaker

Generally you don't pass user/pass in a querystring either ...instead use a submit form..the request should be the same regardless..and like he says..if you dont have a value when you check the request..then redirect the page back to the login..or error

Originally Posted By: Arkh

Everytime you use mysql_* php function or use raw form input data, baby raptor Jesus kills a puppy.

LOL

Good post Zarad! I will typical set my website paswords to: ";DROP TABLE Users" just for some good clean fun ...

Quote:

Generally you don't pass user/pass in a querystring either ...instead use a submit form..the request should be the same regardless..and like he says..if you dont have a value when you check the request..then redirect the page back to the login..or error

Well as I have it set up now, it passes login info from a submit form to my admin.php. On the admin site before I generate any html it validates the username and password.

Should I be validating the login info on like another .php page then after that passes then going to the admin page.

IE

login.htm (submit form) -> validate.php -> admin.htm

I guess my question is trying to figure out to not let admin.htm appear to users at all unless the password is validated. Yeah my steps from login.htm to validate.php I can do. But I can still directly goto admin.htm and bypass the login process.

Id post my code but my schools servers are all down for maintenance.

Quote:

For the login ... you have to check for the password post form variable if it is not there ... meaning someone went directly to the process page ... you should redirect to an error page, or back to the login page & present the user with some message.

So you are saying that my actual admin.htm needs to have a check and if it fails take them away to another page?

Also zarad mentioned sql injection. Some guy in my class just did that to my professors website and we were able to just login with like admin access. How can you prevent that?

could I just add a redirect

Code:

<?php
header( 'Location: http://www.yoursite.com/new_page.html' ) ;
?>

in the code that validates the password and just take people away that dont have the password?

you figure this out yet?

What I do it set a sessions when they log in and a session with thier security level. I can later test those values to decide what to display or if they should redirected away (like back to login page).

On login page I set 'validX' (tells me they logged in)
$_SESSION['validX'] = 'validX';

I also check a table for thier security level
$rs = $db->Execute("select * from user_table where name = '$user';");
while (!$rs->EOF) {
$level = $rs->fields[3];
$_SESSION['level'] = $level;
$rs->MoveNext();
}

Than on a html page I can check the value to decide to display it or redirect them. I use the same method for setting search terms as well.

// Authorization check
if (!isset($_SESSION['validX']))
{
$redirect_url = "index.php";
header("Location: $redirect_url");
}

// Security level check.
if ($_SESSION['level'] <> 14) {
$redirect_url = "index.php";
header("Location: $redirect_url");
}

http://us2.php.net/manual/en/book.session.php

Very solid approach!

Yeah got it all done. Was a moderately fun project. I would have done things totally different after finishing it, but hey that's good to know. Especially things involving organization with tables working with different keys and views how to integrate javascript with PHP/HTML a little differently than I am used to.

Thanks a ton for your help, It is due in 29 minutes. 11:59pm est.

That authentication can be cookie spoofed easy as pie. Just fyi.

Originally Posted By: Kay

That authentication can be cookie spoofed easy as pie. Just fyi.

Using what method? Neither Security , nor web programming are my areas of expertise, but the limited attempts I have tried failed.

You'd have to be pretty sophisticated to cookie spoof a session from the server ... for a class project, it should fly through with no problem.

Most of the teachers out there can't even tell you where a cookie is located on the file system ... if you really had to lock down a site, I'd definately use an alternative authentication method then PHP & MySQL

Originally Posted By: Longshanks

You'd have to be pretty sophisticated to cookie spoof a session from the server ... for a class project, it should fly through with no problem.

Most of the teachers out there can't even tell you where a cookie is located on the file system ... if you really had to lock down a site, I'd definately use an alternative authentication method then PHP & MySQL

True enough.

I use the same technique in the real world if there is a fairly easy way to spoof it I would like to know how. Several I did for work are behind a fairly secure firewall for internal use only (which means thousands could access it) but still somewhat sensitive data that I would like to keep secure. I also use it on a home machine exposed to the internet (http/ssh). It has been up for years without a problem, but I used to get up to hundreds ssh hack attempts on a bad day (mostly Chinese), down to a couple nowadays.

I could change scripting lang and db server type but most likely wouldn�t be more secure. Frankly for me I am in more danger of somebody walking out of my apartment with my servers.

If I remember correctly, been years since i played with PHP ...

$_SESSION marker data is stored in a directory on the server, some /temp by default ... if a user can access that directory, the user can spoof the server by creating a local cookie that equals one of the markers in the server's local directory. This marker is suppose to be automatically deleted when the user ends the session (i.e. closes the browser)

That temp dir can be pointed from the default (which a user can potentially know the default location of) to one you create somewhere else on the file system ... this would make it more difficult to find if a user got access to your file system.

The markers have an obscure random filename .... bSSdwwFfwwf .. or such.

so basically making a semi-permanent duplicate?

Longshanks : if someone got access to your system, you have more important things to be afraid of than spoofed cookies.

Originally Posted By: Arkh

Longshanks : if someone got access to your system, you have more important things to be afraid of than spoofed cookies.

... which is why i mentioned it takes a pretty sophisticated user to spoof a session.

But you can either guess the session ID or, the server is setup correctly ride along one (can't remember the exact name) : some server accept parameters like SESSID=blabla to give a session ID if your PC don't accept cookies.
- so, get yourself a session ID, you'll have a page like index.php?SESSIONID=ab8e43c8...
- refresh it like every 5 mn
- give the link to this page to someone who has an account on it
- as you give it with a session ID, the guy has now the same session you have
- wait for him to log in
- sharing the same session, you just hijacked his. It's time to change some password now.

The way to protect from that in php is simple : session_regenerate_id everytime a user log in or out of one of your apps. If you have a special admin page : samething, ask for the password again and regenerate the session ID.