fetching from mysql

Anon

How can i fetch information from my sql.

I need to fetch special rows in the tabe from my database

dolphinsnot

i first suggest you go to either www.php.net or www.mysql.com and read some documentation.

your question is pretty vague, but this might get you started.

//connect to sql server (server, user, pass)

$db = mysql_connect("yourdatabaseserver.net", "your_db_username", "your_db_pass");

//select database your_db on server, after connecting

mysql_select_db("your_db", $db);

//varaible $result is equal to database query:
//SELECT everything FROM tablename where columnname is equal to ___, after connecting

$result = mysql_query("SELECT * FROM table where columnname=\"____\"", $db);

//$variable = get the returned result from our earlier query, rowname, columnname

$variable = mysql_result($result,rowname,"columname");

//see what you've got

echo($variable);

this is all simple, and might not be exactly what you need...on www.php.net, in the function quick reference, look at mysql_fetch_row, mysql_fetch_object, and all the other mysql_functions.

hope this helps at least a little bit.

-dolphinsnot

Anon

Thanks but i already know that stuff

I guess i didn't explain my question.

I know how to connect to mysql and other variables.

I want to view ONLY some fields in the row. and I don't want to display all the rows only 1 and 5.

If i still didn't explin it clearly enough just reply

dolphinsnot

ah, i see. perhaps you are asking about the SELECT statement itself.

try:

$result=mysql_query("SELECT field1, field2, field3 FROM table WHERE primarykey_column='1' AND primarykey_column='5' ORDER BY field1, field2, field3", $db);

$num_of_rows = mysql_num_rows($result);

     if($num_of_rows > 0) {


         while ($row = mysql_fetch_row($result))
                       { 
                       print "Returned field number one is: row[0]
                       "; 
                       print "Returned field number two is: row[1]
                       ";
                       print "Returned field number three is: row[2]
                       ";  
                  }
              }

i think that should help. just replace fields with your fieldnames, table with your tablename, and primarykey_column with your primary key column.

mysql_fetch_row runs through your results and returns an array where each returned field for that row is a numbered chunk of the array starting at 0. if you are only returning rows 1 and 5, the while loop will run twice, once per occurrence of a row.

again - www.php.net - quick reference - mysql_fetch_row, etc.

good luck

-dolphinsnot

steved

You probably want to use an "OR" in your where clause instead of an "AND".

Anon

OK, Thanx for helping me now i will explain my problem as much a i can..

I tring to make a script that allows people to download files.. ((Its counts the downloads, tells the description etc.)).

I have made the database using another download script. THIS SCRIPT will alows me to edit, and add my downloads. So adding or deleting the downloads from the database is no problem.

THe problem is displaying those downloads from the database. Here's how the database looks like.

Database Name - download
Table Name - Downloads

<h1> Database php - table downloads</h1>
<script language="javascript" src="functions.js" > </script>

<table border="0">

<tr>
<th><A HREF="sql.php?server=1&db=php&pos=0&sql_query=SELECT+%2A+FROM+downloads&sql_order=+order+by+DownloadID+ASC&table=downloads">DownloadID</a></th>

<th><A HREF="sql.php?server=1&db=php&pos=0&sql_query=SELECT+%2A+FROM+downloads&sql_order=+order+by+Name+ASC&table=downloads">Name</a></th>
<th><A HREF="sql.php?server=1&db=php&pos=0&sql_query=SELECT+%2A+FROM+downloads&sql_order=+order+by+Description+ASC&table=downloads">Description</a></th>
<th><A HREF="sql.php?server=1&db=php&pos=0&sql_query=SELECT+%2A+FROM+downloads&sql_order=+order+by+FileSize+ASC&table=downloads">FileSize</a></th>
<th><A HREF="sql.php?server=1&db=php&pos=0&sql_query=SELECT+%2A+FROM+downloads&sql_order=+order+by+FileName+ASC&table=downloads">FileName</a></th>
<th><A HREF="sql.php?server=1&db=php&pos=0&sql_query=SELECT+%2A+FROM+downloads&sql_order=+order+by+DownloadCount+ASC&table=downloads">DownloadCount</a></th>
<th><A HREF="sql.php?server=1&db=php&pos=0&sql_query=SELECT+%2A+FROM+downloads&sql_order=+order+by+Category+ASC&table=downloads">Category</a></th>
<th><A HREF="sql.php?server=1&db=php&pos=0&sql_query=SELECT+%2A+FROM+downloads&sql_order=+order+by+Redirect+ASC&table=downloads">Redirect</a></th>
<th><A HREF="sql.php?server=1&db=php&pos=0&sql_query=SELECT+%2A+FROM+downloads&sql_order=+order+by+Stored+ASC&table=downloads">Stored</a></th>
</tr>
<tr bgcolor=#DDDDDD><td align=right> 1 </td>
<td> Adobo Dimensions </td>
<td> checking </td>
<td align=right> 1234 </td>
<td> http://localhost/mmm.zip </td>
<td align=right> 526 </td>
<td align=right> 1 </td>
<td>  </td>
<td> Yahoo </td>
<td><a href="tbl_change.php?primary_key=+DownloadID+%3D+%271%27+&server=1&db=php&table=downloads&pos=0&sql_query=SELECT+%2A+FROM+downloads&goto=sql.php">Edit</a></td><td><a href="sql.php?sql_query=DELETE+FROM+downloads+WHERE++DownloadID+%3D+%271%27+&server=1&db=php&table=downloads&pos=0&goto=sql.php%3Fserver%3D1%26db%3Dphp%26table%3Ddownloads%26pos%3D0%26sql_query%3DSELECT+%2A+FROM+downloadszero_rows=The+row+has+been+deleted&goto=db_details.php">Delete</a></td></tr>
<tr bgcolor=#CCCCCC><td align=right> 2 </td>
<td> Macromedia Dreamwaeaver </td>
<td> One of the best programs to do web designing </td>
<td align=right> 11112 </td>
<td> http://localhost/aaa.zip </td>
<td align=right> 4 </td>
<td align=right> 1 </td>
<td>  </td>
<td> Geocities </td>
<td><a href="tbl_change.php?primary_key=+DownloadID+%3D+%272%27+&server=1&db=php&table=downloads&pos=0&sql_query=SELECT+%2A+FROM+downloads&goto=sql.php">Edit</a></td><td><a href="sql.php?sql_query=DELETE+FROM+downloads+WHERE++DownloadID+%3D+%272%27+&server=1&db=php&table=downloads&pos=0&goto=sql.php%3Fserver%3D1%26db%3Dphp%26table%3Ddownloads%26pos%3D0%26sql_query%3DSELECT+%2A+FROM+downloadszero_rows=The+row+has+been+deleted&goto=db_details.php">Delete</a></td></tr>
</table>

<table border=0></table>

I got this from phpmyadmin.

Now i want to display the information on a html or php page

Anon

Do you understand it more do i need to make it more clear..

Anon

hmmm...i think i understand what all that does...it's extremely messy from the cut and paste.

i don't quite know what to tell you that i didn't explain before...here, i'll give you the code for a very simple site search engine i recently wrote, and hope that you will be able to adapt it to suit your needs.

the search engine is fed keywords, searches all the tables in the database for those keywords, and returns all results. the only real difference between what it does and what you need is that what it looks for varies depending on what the user stuck in the $search variable via an input field. the script starts out knowing what $search is because it grabs is from the URL (www.website.org/search.php3?search=whatever+whatever)

<?php

//$plus is a space

     $plus = " ";

//$search_words is an array. it is created by the php function 'split([remove all spaces from], [$search])'

$search_words = split($plus, $search);

//$search_num is how many search words there were

     $search_num = count($search_words);

//$table is an array that contains all the different tables that are searched

     $table = array("main","ilinks","about","fbs","awards","finder","news");

//$table_num is the number of tables in $table

     $table_num = count($table);

//print to browser: Your Search Results for "whatever whatever"

printf("<h1>Your Search Results for: \"$search\"</h1>");

//begin looping through the tables
/
for ([start internal pointer at the first thing in the array]; [as long as the loop has gone through itself less times than $table_num]; [keep looping]){
/

     for ($i = 0; $i < $table_num; ++$i)
     {

/
start building mysql query. we'll add to $statement later, for right now it equals: SELECT tbl, page, title, description FROM [$table[loop number in array]] WHERE keywords LIKE '%[$search_words[first in array]%'
/

     $statement = "SELECT tbl, page, title, description FROM $table[$i] WHERE keywords LIKE '%$search_words[0]%' ";

//if there was more than one search word (whatever+whatever instead of just whatever) add those different search words to $statement via the .= operator. $idx acts the same here as $i did above

      if ($search_num > 1){
     for ($idx = 1; $idx < $search_num; ++$idx){
     $statement .= " OR keywords LIKE ";
     $statement .= "'%$search_words[$idx]%' ";
     }
     }

/
do the actual search
/

//stuff for connecting to the database

     $db = mysql_pconnect("servername", "user", "pass");
     mysql_select_db("database", $db);

//$result is equal to the query result. a mysql query is done using $statement assembled above and $db established above

      $result = @mysql_query($statement, $db);

//$num_of_rows = however many table rows were returned above in $result

     $num_of_rows = mysql_num_rows($result);

//if ([more than zero rows were returned, continue]){

     if($num_of_rows > 0) {

/
KILLAH, this is where the results actually get spit out to the web browser. study it.
/

//do the stuff in curly brackets once for each row returned from the database table. $row is an array containing all the different fields returned for the current row being processed by the loop, if you have 2 rows returned, it'll spit out 2 results

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

/*
print the following html code to browser:
<p>
<a href="../phpfile.php3/page.html>The Stuff Returned from the 'title' table column in this row </a><br>
The stuff Returned from the 'description' table column in this row<br>
<font color=#57A275>www.website.org/phpfile.php3/page.html</font>
</p>

                       print "<p><a href=\"../$row[0].php3/$row[1].html\">
                       $row[2]</a><br>$row[3]<br><font color=#57A275>www.website.org/$row[0].php3/$row[1].html</font></p>
                       ";

//these curly brackets end all the loops that are still running, once they are done looping.

                  }
              }
           }

whew! that was quite a task...i didn't have any comments in the code above until just now...in case you didn't know // and /,/ are comment indicators.

anyhow, the end result to the thing above looks almost indentical to the results you would get from searching for 'whatever' on google or altavista or lycos or something. there isn't anything for error handling, and i've still got lots of room to improve on it, but i just started learning PHP this month, and i'm happy with what it does for now.

if this still doesn't make sense, i don't think i can clarify much further, but i'll try. let me know if it does or doesn't work.

good luck

-dolphinsnot

Anon

thanx

i try to change it and suit my needs

dolphinsnot

great. does that all make some sense to you? does it look like to code i gave you does something similar to what you need done? do you have any further questions about it?

i've only tested that whole thing in one environment, and would like to know what sort of luck you have implementing some of it.

please keep us all posted on your progress.

-dolphinsnot

Anon

Well, tell you the truth.. I found the code from the site u gave me Zend PHP MAnual.

Now i ma stuck on gettin the result from 1 row only. Here is the code i use..

<?php

$db = mysql_connect("localhost", "root");
mysql_select_db("php",$db);
$result = mysql_query("SELECT * FROM Downloads",$db);if (!$result) { echo("<P>Error performing query: " . mysql_error() . "</P>"); exit();}

while ( $row = mysql_fetch_array($result) ) { echo("<P>" . $row["Name"] . "</P>");}

THE above code fetches for all the field (NAme) in the table.

but i only want to to fetch from row 1. Can u modify that code so it does that..
It only fetches the field from row 1

dolphinsnot

great progress.

try this:

<?php

$db = mysql_connect("localhost", "root");
mysql_select_db("php",$db);

/*
SELECT everything FROM Downloads WHERE [primary_key]=1 OR [primary_key]=5

Make sure that in phpMyAdmin you set whatever column has the row numbers (rowid?) as your primary key for that table

i made this SELECT statement to handle rows 1 and 5
*/

$result = mysql_query("SELECT * FROM Downloads WHERE rowid=\"1\" OR rowid=\"5\"",$db);

if (!$result) {

echo("<P>Error performing query: " . mysql_error() . "</P>"); 

exit();

}

//try fetch_row instead

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

/*
i'm assuming you're using phpMyAdmin for mysql administration.

fetch_row will return your columns in the same order phpMyAdmin does, left to right. it puts whatever is in column 1 in $row[0], whatever was in column 2 in $row[1], whatever was in column 3 in $row[2], etc, etc ($row[row # -1]).

if you wanted to print column 1 on the first line, and columns 2 and 3 on the second you would do the following:
*/

echo("<P>$row[0]<BR>$row[1]$row[2]</P>");

i think that should do it. it will return one result for row 1, and one result for row 5

let me know how it works for you.

--dolphinsnot

Anon

What is a primary key ???

dolphinsnot

a primary key is one column in your database table that is used for finding everything. for big tables, you can set it to auto_increment, which means mysql will automatically your rows for you in the order they are entered.

for the website i'm working on, i've got a column called 'page' that i use as my primary key. it holds strings instead of numbers (index, about, foobar), but works the same.

it's a good idea to have one...in phpMyAdmin, if you already have a column that has the numbers of your rows in it, just go into properties for that column and check the primary box.

if you don't already have a column that has your row numbers in it, it would be a good idea to add a column to the beginning of your table and make it your key.

i'm not 100% certain it is required for all functions, but i think it's a good practice to get in to.

by the way: www.google.com is a great search engine. ask it about things like primary key, and it will probably give you a lot more information than i can. www.php.net and www.mysql.com and www.phpbuilder.com have also taught me 90% of this stuff. search. research.

if i teach you how to fish, you'll eat for a lifetime. if i feed you a fish, you'll only eat today.

🙂

-dolphinsnot