i have 4 columns in the mysql database

url, showingtext, target

want to make a hyperlink from those columns:

question: how do i automatically fill the 4th column containing the html hyperlink code

question: should i rather string the 3 columns togheter using php, how would one do that

sorry this might be a mysql question rather than a php question, i also found out that there is tons of info on the web. if you have a simple solution anyway I would be thankful, greetings

You could just do something like:

UPDATE `tablename`
SET `hyperlink` = CONCAT('<a href="', `url`, '" target="', `target`, '">', `showingtext`, '</a>')
WHERE `hyperlink` IS NULL
OR `hyperlink` = '';

But make sure you back up your tables first, just in case this doesn't do what you want.

R.

You could just do something like:

UPDATE `tablename`
SET `hyperlink` = CONCAT('<a href="', `url`, '" target="', `target`, '">', `showingtext`, '</a>')
WHERE `hyperlink` IS NULL
OR `hyperlink` = '';

But make sure you back up your tables first, just in case this doesn't do what you want.

R.

the problem is that it doesnt insert anything in the column link, any idea why

<html>
<body>

<form action="insert.php" method="post">
url: <input type="text" name="url" />
target: <input type="text" name="target" />
showingtext: <input type="text" name="showingtext" />
<input type="submit" />
</form>
<?php
UPDATE `hyperlinktable`
SET `hyperlink` = CONCAT('<a href="', `url`, '" target="', `target`, '">', `showingtext`, '</a>')
WHERE `hyperlink` IS NULL
OR `hyperlink` = '';
?>

</body>
</html>

From your original post, I understood it that you already had the first three columns of each row populated in the database. Is this not the case?

The above won't work. It will just output the content to the page. Instead, validate the values being submitted via the form, build the insert query and execute it using something like the mysql_query() function.

R.

From your original post, I understood it that you already had the first three columns of each row populated in the database. Is this not the case?

The above won't work. It will just output the content to the page. Instead, validate the values being submitted via the form, build the insert query and execute it using something like the mysql_query() function.

R.

gotcha
i guess i still need to add some sort of print 'hyperlink' thingi to actually see it,because i dont actually see it, i understand now that its just for output and isnt sent to the database yet
please forgive my stupidness

so in order to actually save it in the database column hyperlink, which would be great it needs another function....thanks sofar

p.s. so nice to be your php idiot

Exactly, you need to process the form submission. A quick and dirty example of that might be:

<?php // Assume this is the insert.php script ?>

<?php 
if($_POST) {
    // I suggest you validate all POST values
    extract($_POST);
    $hyperlink = sprintf('<a href="%1$s" target="%2$s">%3$s</a>',
        $url, $target, $showingtext);
    
    $sql = sprintf("INSERT INTO `hyperlinktable` SET `url` = '%1$s', 
        `target` = '%2$s', `showingtext` = '%3$s', `hyperlink` = '%4$s'",
        $url, $target, $showingtext, $hyperlink);

    mysql_query($sql);
}
?>

<html>
    <head>
        <title>Example</title>
    </head>

    <body>
        <form action="insert.php" method="post">
            url: <input type="text" name="url" />
            target: <input type="text" name="target" />
            showingtext: <input type="text" name="showingtext" />
            <input type="submit" />
        </form>

        <?php if($_POST): ?>
            <?php echo $hyperlink; ?>
        <?php endif; ?>
    </body>
</html>

R.

Hyperlink are made for linking one page with another...but the code which you have shown is really wonderful to refer.

I can give you the insert.php code that I'am using maybe i cant really figure out how to include the code you showed me ontop to get it working, however it does put it the three values in the database but doesnt put in the hyperlink

<?php
$con = mysql_connect("blabla.db.7649698.hostedresource.com","username","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("mydatabase", $con);

$sql="INSERT INTO hyperlinktable (url, target,showingtext)
VALUES
('$_POST','$_POST[target]','$_POST[showingtext]')";

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "1 website added, thank you";

mysql_close($con)
?>

hope somebody can somehow melt the stuff together, thanks sofar for support

Adding your database connect and select functions to the top of the code I provided should do the trick:

<?php // Assume this is the insert.php script ?>

<?php 
if($_POST) {
    $db = mysql_connect('blabla.db.7649698.hostedresource.com','username','password');

    if (!$db)
        die('Could not connect: ' . mysql_error());

    mysql_select_db('mydatabase', $db);

    // I suggest you validate all POST values
    extract($_POST);
    $hyperlink = sprintf('<a href="%1$s" target="%2$s">%3$s</a>',
        $url, $target, $showingtext);
    
    $sql = sprintf("INSERT INTO `hyperlinktable` SET `url` = '%1$s', 
        `target` = '%2$s', `showingtext` = '%3$s', `hyperlink` = '%4$s'",
        $url, $target, $showingtext, $hyperlink);

    mysql_query($sql);
}
?>

<html>
    <head>
        <title>Example</title>
    </head>

    <body>
        <form action="insert.php" method="post">
            url: <input type="text" name="url" />
            target: <input type="text" name="target" />
            showingtext: <input type="text" name="showingtext" />
            <input type="submit" />
        </form>

        <?php if($_POST): ?>
            <?php echo $hyperlink; ?>
        <?php endif; ?>
    </body>
</html>

R.

question: if this is the insert.php we have twice the connect function in it also I didnt quite understand what you mean with validating the post values, but skipp my really stupid questions I can only catch up with you guysngirls

<?php
$con = mysql_connect("blabla.db.7649698.hostedresource.com","username","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}


mysql_select_db("her goes the database", $con);


$sql="INSERT INTO hyperlinktable (url, target,showingtext)
VALUES
('$_POST','$_POST[target]','$_POST[showingtext]')";


if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "1 hyperlink added, thank you";


mysql_close($con)
?>
<?php
if($_POST) {
$db = mysql_connect('blabla.db.7649698.hostedresource.com','username','password');


if (!$db)
die('Could not connect: ' . mysql_error());


mysql_select_db('mydatabase', $db);


// I suggest you validate all POST values
extract($_POST);
$hyperlink = sprintf('<a href="%1$s" target="%2$s">%3$s</a>',
$url, $target, $showingtext);


$sql = sprintf("INSERT INTO `hyperlinktable` SET `url` = '%1$s',
`target` = '%2$s', `showingtext` = '%3$s', `hyperlink` = '%4$s'",
$url, $target, $showingtext, $hyperlink);


mysql_query($sql);
}
?>

You only need the code once. You seem to have duplicated the code and included it twice.

Also, don't forget to validate the $_POST input values before using the values in a database query.

R.

First use Code tags in your posts. Secondly try to read tutorial in this link, since you are new, to get rough Idea of what you are supposed to do!

this is the insert.php wich put in the three values but is missing the insert of the hyperlink into the database

this is the insert.php or inserthyperlink.php it put in the 3 values but missing the 4th value, i think I am turning in circles now

<?php
$con = mysql_connect("jn.db.7649698.hostedresource.com","johnn","pass");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("mydatabase", $con);

$sql="INSERT INTO hyperlinktable (url, target,showingtext)
VALUES
('$_POST[url]','$_POST[target]','$_POST[showingtext]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 website added, thank you";

mysql_close($con)
?> <?php
if($_POST) {
    $db = mysql_connect('.db.7649698.hostedresource.com','user','pass');

    if (!$db)
        die('Could not connect: ' . mysql_error());

    mysql_select_db('mydatabase', $db);

    // I suggest you validate all POST values
    extract($_POST);
    $hyperlink = sprintf('<a href="%1$s" target="%2$s">%3$s</a>',
        $url, $target, $showingtext);

    $sql = sprintf("INSERT INTO `hyperlinktable` SET `url` = '%1$s',
        `target` = '%2$s', `showingtext` = '%3$s', `hyperlink` = '%4$s'",
        $url, $target, $showingtext, $hyperlink);

    mysql_query($sql);
}
?>

Hi John,

I think you are getting confused. The code you have posted above is essentially doing the same thing twice. The code I posted previously (and again below) should do what you want.

I have added annotations this time so you can follow it through more clearly in case you're in any doubt as to how it functions.

<?php // insert.php

// Check whether POST array is populated
if($_POST) {
    // POST array populated, prepare database connection
    $db = mysql_connect('blabla.db.7649698.hostedresource.com','username','password');

    if (!$db)
        die('Could not connect: ' . mysql_error());

    mysql_select_db('mydatabase', $db);

    // Extract variables from POST array into individual variables
    extract($_POST);

    // At this stage you should validate that the variables contain what you expect. If not display errors to the user accordingly.

    // Build the hyperlink. This function will replace each placeholder with the listed variables values respectively
    $hyperlink = sprintf('<a href="%1$s" target="%2$s">%3$s</a>',
        $url, $target, $showingtext);
    
    // Build the SQL query. This function will again replace each placeholder with the listed variable values respectively
    $sql = sprintf("INSERT INTO `hyperlinktable` SET `url` = '%1$s', 
        `target` = '%2$s', `showingtext` = '%3$s', `hyperlink` = '%4$s'",
        $url, $target, $showingtext, $hyperlink);

    // Execute the query
    mysql_query($sql);
}
?>

<html>
    <head>
        <title>Example</title>
    </head>

    <body>
        <?php if($_POST): ?>
            <p>A new link has been created: <?php echo $hyperlink; ?></p>
        <?php endif; ?>

        <form action="insert.php" method="post">
            url: <input type="text" name="url" />
            target: <input type="text" name="target" />
            showingtext: <input type="text" name="showingtext" />
            <input type="submit" />
        </form>
    </body>
</html>

Do you understand how this code works? The content of the if statement is only executed when the new hyperlink form is submitted.

R.

The link I gave above teaches step by step exactly what OP is trying to do and he/she completely ignored it!

i visited the tutorial, that helps. yes i am a bit confused, to bring it to the point

i made a insert.php file containing the first 30 lines or so
i made a rex.html page containing the rest.

i put the whole code on one html page, smart as I am it didnt work, how can it because the html page asks for a insert.php which i guess needs to be an external php file.

i will quit for today, but will check agein the first version which as you say works, probably.

No, it doesn't need to be an external PHP file. The form posts back to the same script, where the processing code is only executed when the form is submitted.

R.

No, it doesn't need to be an external PHP file. The form posts back to the same script, where the processing code is only executed when the form is submitted.

R.

But why is a insert.php used within the html page, if there is non, even on the top part there is no insert.php written except in your descriptions, that i dont get

Create a PHP file called insert.php and paste the code I have written, lines 1-49 into the file.

Visit localhost/insert.php, or equivalent, and it should render the form. If you submit the form, it will reload the page, and should process the data you entered.

R.

after submitting the three values how nice I get this message

A new link has been created: godaddy
i didnt expect the root url to be in there as well but just the url i submitted so http://ww.godaddy.com


check it out on iglobe.info/insert.php

if i check whats in the database after submitting i get this
in the url column of the table appears this: ,`target` =
in the target column of the table appears nothing
in the showingtext column of the table is this: , `hyperlink` =
in the hyperlink column of the table appears nothing

hope its a fast fix in the code thanks for hanging in there with me sofar, would be nice to get it finished for others to come

i understand though that a this insert.php file can call itself within the code, right?

this is exactly the code used, so it might have mistakes to be fixed

<?php // insert.php

// Check whether POST array is populated
if($_POST) {
    // POST array populated, prepare database connection
    $db = mysql_connect('...n.db.7649698.hostedresource.com','...','...');

    if (!$db)
        die('Could not connect: ' . mysql_error());

    mysql_select_db('here the database', $db);

    // Extract variables from POST array into individual variables
    extract($_POST);

    // At this stage you should validate that the variables contain what you expect. If not display errors to the user accordingly.

    // Build the hyperlink. This function will replace each placeholder with the listed variables values respectively
    $hyperlink = sprintf('<a href="%1$s" target="%2$s">%3$s</a>',
        $url, $target, $showingtext);

    // Build the SQL query. This function will again replace each placeholder with the listed variable values respectively
    $sql = sprintf("INSERT INTO `hyperlinktable` SET `url` = '%1$s',
        `target` = '%2$s', `showingtext` = '%3$s', `hyperlink` = '%4$s'",
        $url, $target, $showingtext, $hyperlink);

    // Execute the query
    mysql_query($sql);
}
?>

<html>
    <head>
        <title>Example</title>
    </head>

    <body>
        <?php if($_POST): ?>
            <p>A new link has been created: <?php echo $hyperlink; ?></p>
        <?php endif; ?>

        <form action="insert.php" method="post">
            url: <input type="text" name="url" />
            target: <input type="text" name="target" />
            showingtext: <input type="text" name="showingtext" />
            <input type="submit" />
        </form>
    </body>
</html>

Strange... for some reason the SQL parameters are not being bound correctly. I've amended it and the following works for me:

<?php // insert.php

// Check whether POST array is populated
if($_POST) {
    // POST array populated, prepare database connection
    $db = mysql_connect('blabla.db.7649698.hostedresource.com','username','password');

    if (!$db)
        die('Could not connect: ' . mysql_error());

    mysql_select_db('mydatabase', $db);

    // Extract variables from POST array into individual variables
    extract($_POST);

    // At this stage you should validate that the variables contain what you expect. If not display errors to the user accordingly.

    // Build the hyperlink. This function will replace each placeholder with the listed variables values respectively
    $hyperlink = sprintf('<a href="%1$s" target="%2$s">%3$s</a>', $url, $target, $showingtext);
    
    // Build the SQL query. This function will again replace each placeholder with the listed variable values respectively
    $sql = sprintf("INSERT INTO `hyperlinktable` SET `url` = '%s', `target` = '%s', `showingtext` = '%s', `hyperlink` = '%s'",
        $url, $target, $showingtext, $hyperlink);

    // Execute the query
    mysql_query($sql);
}
?>

<html>
    <head>
        <title>Example</title>
    </head>

    <body>
        <?php if($_POST): ?>
            <p>A new link has been created: <?php echo $hyperlink; ?></p>
        <?php endif; ?>

        <form action="insert.php" method="post">
            url: <input type="text" name="url" />
            target: <input type="text" name="target" />
            showingtext: <input type="text" name="showingtext" />
            <input type="submit" />
        </form>
    </body>
</html>

R

yes it works how nice, the database was filled delaid so I was receiving older data from not working code. the table is fine, so funny that the link on the page has the main root web included, do you think it would help to put the hypelink into <html> </html>,
i can still try it out however thanks so much, greetings

Strange... for some reason the SQL parameters are not being bound correctly. I've amended it and the following works for me:

<?php // insert.php

// Check whether POST array is populated
if($_POST) {
    // POST array populated, prepare database connection
    $db = mysql_connect('blabla.db.7649698.hostedresource.com','username','password');

    if (!$db)
        die('Could not connect: ' . mysql_error());

    mysql_select_db('mydatabase', $db);

    // Extract variables from POST array into individual variables
    extract($_POST);

    // At this stage you should validate that the variables contain what you expect. If not display errors to the user accordingly.

    // Build the hyperlink. This function will replace each placeholder with the listed variables values respectively
    $hyperlink = sprintf('<a href="%1$s" target="%2$s">%3$s</a>', $url, $target, $showingtext);
    
    // Build the SQL query. This function will again replace each placeholder with the listed variable values respectively
    $sql = sprintf("INSERT INTO `hyperlinktable` SET `url` = '%s', `target` = '%s', `showingtext` = '%s', `hyperlink` = '%s'",
        $url, $target, $showingtext, $hyperlink);

    // Execute the query
    mysql_query($sql);
}
?>

<html>
    <head>
        <title>Example</title>
    </head>

    <body>
        <?php if($_POST): ?>
            <p>A new link has been created: <?php echo $hyperlink; ?></p>
        <?php endif; ?>

        <form action="insert.php" method="post">
            url: <input type="text" name="url" />
            target: <input type="text" name="target" />
            showingtext: <input type="text" name="showingtext" />
            <input type="submit" />
        </form>
    </body>
</html>

R

Well, did you enter the protocol before the link? E.g. http://blocblue.com If not, the browsers will assume its a link relative to the current page.

R.

If you're posting a link to that code on a live server, I really hope you did as I said and added validation for the input elements, otherwise you're likely to get hacked, especially as the source is published here for all to see.

R.

for whoever wants to see how it looks like iglobe.info/insert.php . more to come like always. chears

i inserted the http:// after the href,to get the absolute url but i still get this problem

= sprintf('<a href=http://"%1$s" target="%2$s">%3$s</a>', $url, $target, $showingtext);


%22www.google.com%22 example when I enter www.google.com

in the table it is stored as

<html><a href=http://"www.google.com" target="main">googlet</a></html>

any clou

sorry, i really dont have a clou how to add validation for security reasons

Try moving the http:// inside the quotation marks!

// Instead of this...
sprintf('<a href=http://"%1$s" target="%2$s">%3$s</a>', $url, $target, $showingtext);

// Try this...
sprintf('<a href="http://%1$s" target="%2$s">%3$s</a>', $url, $target, $showingtext);

But also consider instances where the protocol is https://

Google for validating form inputs using PHP.

R.

Try moving the http:// inside the quotation marks!

// Instead of this...
sprintf('<a href=http://"%1$s" target="%2$s">%3$s</a>', $url, $target, $showingtext);

// Try this...
sprintf('<a href="http://%1$s" target="%2$s">%3$s</a>', $url, $target, $showingtext);

But also consider instances where the protocol is https://

Google for validating form inputs using PHP.

R.

yes that worked fine

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.