external links in php + mysql query code.

[sayakyi]

Dear friend,

I make a very simple dictionary script. Now I need to extend sound file in this script.

My Initial database has only 2 colums

{English,Bm}
I forget to create the "ID" column. :)

all 5000 sound files are stored in a folder

http://www.domain.com/files/sound/(english-word)*.mp3

how can I link all the English words sound files in my SQL query code.?

echo '<li>$row['engword']'</a></li>';

engword = english word.

many thanks in advance,

koko

[diafol]

why don't you add the id column now? Get a copy of SQLyog from webyog (the community version is free) and do Alter Table - insert an id field (autonumber, int). However this won't help too much with linking to a sound file:

It looks as though you're gonna need the actual word from db as the basis for your filename. What about special characters and apostrophes??

Anyway:
...do your db connection as get result into '$resultset'...
...set up an unordered list (<ul>)and populate it with list items as below:

while($data = mysql_fetch_array($resultset)){
    echo "<li><a href=\"http://www.domain.com/files/sound/{$data['engword']}.mp3\" title=\"\">{$data['engword']}</a></li>\n";
}

Remember to give a title to your <a> tag. Also, be careful to clean your data from the db (not shown above).

[cwarn23]

Depending on your dictionary I am guessing you will need a numbered id field if there are non a-z0-9 characters. To solve this, the following script will automatically fill in the id field for you:

<?
$username='admin'; //Mysql access username
$password=''; //Mysql access password
$database='mydatabase'; //Database to connect to - not the table name.
$tablename='mytable'; //the name of the table - not the database.
$idfieldname='id'; //the column name for the id field.
//configure above variables

//DO NOT CHANGE THE BELOW
//==========================
mysql_connect('localhost', $username, $password) or die('Invalid login details.<hr>'.mysql_error());
mysql_select_db($database) or die('Cannot connect to selected database.<hr>'.mysql_error()); 
mysql_query('UPDATE `table` SET `'.$idfieldname.'`=""') or die(mysql_error());
$result=mysql_query('SELECT * FROM `'.$tablename.'`');
if (mysql_num_rows($result)>0) {
    for ($row=1; $data=mysql_fetch_assoc($result); $row++) {
        $where='';
        foreach ($data AS $key => $val) { 
            $where.='`'.$key.'`="'.mysql_real_escape_string($val).'" AND';
            }
        $where=substr($where,0,-3);
        $where.='LIMIT 1';
        mysql_query('UPDATE `'.$tablename.'` SET `'.$idfieldname.'`="'.$row.'" WHERE '.$where) or die(mysql_error());
        unset($where);
        unset($key);
        unset($val);
        }
    echo 'ID field updated';
    } else {
    echo 'Nothing to update';
    }
?>

I made this script as part of a mysql tutorial. Hope it helps.

[sayakyi]

why don't you add the id column now? Get a copy of SQLyog from webyog (the community version is free) and do Alter Table - insert an id field (autonumber, int). However this won't help too much with linking to a sound file:

It looks as though you're gonna need the actual word from db as the basis for your filename. What about special characters and apostrophes??
below:

while($data = mysql_fetch_array($resultset)){
    echo "<li><a href=\"http://www.domain.com/files/sound/{$data['engword']}.mp3\" title=\"\">{$data['engword']}</a></li>\n";
}

Remember to give a title to your <a> tag. Also, be careful to clean your data from the db (not shown above).

Hi ardav,

Thankyou verymuch for your code and the SQLyog Software. It work.!
I will try to re-design my table.

I wann ask you the code..

echo "<li><a href=\"http://www.domain.com/files/sound/{$data['engword']}.mp3\" title=\"\">{$data['engword']}</a></li>\n";

title=\"\"

What it does for?

How can I add open new windows pop in this links?
I have a javascript fuction rel="PopWin" - but where to add this in my code? :)

echo "<li><a href=\"http://www.domain.com/files/sound/{$data['engword']}.mp3\" title=\"\">{$data['engword']}</a></li>\n";

Depending on your dictionary I am guessing you will need a numbered id field if there are non a-z0-9 characters. To solve this, the following script will automatically fill in the id field for you:

...code...

I made this script as part of a mysql tutorial. Hope it helps.

Hi cwarn23,

Thank You very much for this code.

I got this error.

Table 'mydic.table' doesn't exist

Many thank in advance,

koko

[diafol]

'title' is the html attribute for providing a link (and other elements) with an accessible description. You should think about giving a suitable description, e.g. 'open the audio file for the word {$data}'.

As for the js popup function, sorry don't know enough to help you.

[sayakyi]

hi ardav,

I can add it now the html target='_blank' to open a new window in my link.

Thankyou.

regards,
koko

[cwarn23]

I just checked the script for that error and turns out that the table variable wasn't assigned to the mysql query. So the following updated script should do the job providing the variables at the top are configured correctly.

<?
$username='admin'; //Mysql access username
$password=''; //Mysql access password
$database='mydatabase'; //Database to connect to - not the table name.
$tablename='mytable'; //the name of the table - not the database.
$idfieldname='id'; //the column name for the id field.
//configure above variables

//DO NOT CHANGE THE BELOW
//==========================
mysql_connect('localhost', $username, $password) or die('Invalid login details.<hr>'.mysql_error());
mysql_select_db($database) or die('Cannot connect to selected database.<hr>'.mysql_error()); 
mysql_query('UPDATE `'.$tablename.'` SET `'.$idfieldname.'`=""') or die(mysql_error());
$result=mysql_query('SELECT * FROM `'.$tablename.'`');
if (mysql_num_rows($result)>0) {
    for ($row=1; $data=mysql_fetch_assoc($result); $row++) {
        $where='';
        foreach ($data AS $key => $val) { 
            $where.='`'.$key.'`="'.mysql_real_escape_string($val).'" AND';
            }
        $where=substr($where,0,-3);
        $where.='LIMIT 1';
        mysql_query('UPDATE `'.$tablename.'` SET `'.$idfieldname.'`="'.$row.'" WHERE '.$where) or die(mysql_error());
        unset($where);
        unset($key);
        unset($val);
        }
    echo 'ID field updated';
    } else {
    echo 'Nothing to update';
    }
?>

[sayakyi]

thx again.

regards,
koko