vb.org Archive - Showing MySQL rows in a PHP file

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - Showing MySQL rows in a PHP file (https://vborg.vbsupport.ru/showthread.php?t=138585)

KingPuyol

02-06-2007 04:47 AM

Showing MySQL rows in a PHP file

How can I show MySQL rows in a PHP file?

Like if I have a table called "coders" and in it I have "coderid", "codername", and "codercountry"

and the values are:
1 - John - USA
2 - Mary - England
3 - Jordi - Spain
etc etc.

I want to show ALL THE ROWS in the following format:

<a href="coder.php?show="'$coderid'">'.$codername.' ('.$codercountry.')</a>
Or whatever way you can write it.

So I want the HTML output to be:

Code:

<a href="coder.php?show=1">John (USA)</a>

<a href="coder.php?show=2">Mary (England)</a>

<a href="coder.php?show=3">Jordi (Spain)</a>

So who can this be done, I want to show ALL THE ROWS.

Dismounted

02-06-2007 05:32 AM

I'm presuming you're using the vBulletin backend in the following example.

PHP Code:


		
			
// cache data
$tabledata = $vbulletin->db->query_read("SELECT * FROM `coders`");
$tabledata = $vbulletin->db->fetch_array($tabledata);

// display rows
foreach ($tabledata AS $row)
{
echo '<a href="coder.php?show=' . $row['coderid'] . '">' . $row['codername'] . '  (' . $row['codercountry'] . ')</a>';
}

KingPuyol

02-06-2007 10:42 AM

Thanks but can you show me something without the vBulletin backend, I understood how to do it but if you can show me another example without the vBulletin backend then it will be much easier.

firstrebel

02-06-2007 02:23 PM

Code:

<?php



// change hostname, username and password, dbname



if(!isset($query) || empty($query))

        ($query= "select coderid,codername,codercountry from coders order by coderid asc");        

        $query = stripslashes($query);

        $db_link = mysql_connect('hostname', 'username', 'password')

                  or die("Could not connect to database. Please try again later.");

        mysql_select_db('dbname') or 

                die("could not connect to database");

        $result = mysql_query($query) or        

                die( mysql_error() );

        $number_cols = mysql_num_fields($result);

        

        echo "<table width=740  border=1 align=center cellspacing=0 cellpadding=5>\n";

        echo "<tr align=center>\n"; 

        echo "<font face=arial>";

        echo "<font size=-1>";        

        for ($i=0; $i<$number_cols; $i++)

        {

                echo "<th>" .mysql_fieldname($result, $i). "</th>\n"; 

        }

        echo "</tr>\n";

        

        while ($row = mysql_fetch_row($result))

        {

                echo "<tr align=left>\n";



                for ($i=0; $i<$number_cols; $i++)

                {

                        echo "<td>";

                        echo "<font face=verdana>";

                        echo "<font size=-1>";                

                        if (!isset($row[$i]))

                                {echo "n.a.";}

                        else 

                                {echo $row[$i];}

                        echo "</td>\n"; 

                }

                echo "</tr>\n";

        }

        echo "</table>";

        echo "</font>";

      mysql_close($db_link);

?>

KingPuyol

02-07-2007 09:00 AM

Thanks and can you please explain what is $i and can you also tell me:
1- How can I limit the number of rows to show, for example: showing the last 20 rows.
2- Paginating rows, for example, showing first 20 rows and then the next 20 in the other. etc..
3- If one of the fields that I have is empty, for example let's say the table is called table and the column which is going to be empty is called game. How can I select the empty one:
Here's the table:

Code:

id----  player      ---- game

1 ----  Andrew    ---- 5

2 ----  Someone  ---- 3

3 ----  King          ---- NULL*

4 ----  MOnkey    ---- NULL

I meant by NULL that it's EMPTY, nothing is in it, so if I wanted to arrange all this in Ascending Order by ID and I want it to select the FIRST row where the game field is NULL.

firstrebel

02-07-2007 08:31 PM

To limit the return you would use LIMIT is the SELECT statement

Code:

$query= "SELECT coderid,codername,codercountry from coders order by coderid asc LIMIT 20"

This would return the last 20 in an ascending order. To get the last 20 in descending order change asc LIMIT 20 to desc LIMIT 20.

To get a specific range you would use asc LIMIT 1,20 which would give rows 1-20, or asc LIMIT 20,40 would give rows 20-40. Play around until you get what you want by changing asc and desc and the LIMIT parameters.

I don't have a pagination script in working order at the moment, it is something I need to get working on one of my dbs that has around 2200 rows. I have a script but not integrated into the rest of the script.

In the SELECT statement you specify all the fields you want called, whether they have data in them or not. Any that are empty will just have an empty cell in the row.

Try this as your select statement from your example where the table fields are id, player, game:

($query= "select id, player, game from tablename order by id asc");

Change tablename to whatever it is in the db.

Bob

KingPuyol

02-08-2007 06:19 AM

Thanks, I really appreciate it.

All times are GMT. The time now is 10:25 AM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01040 seconds Memory Usage 1,737KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (4)bbcode_code_printable (1)bbcode_php_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (7)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: