ID to GUID

[cdmafra]

Hello.

 

I'm writing this post to ask a question: how to "transform" ID fields to GUID? Is there any solution? If not, how can I create a GUID field/column in my MySQL table?

[fenway]

Why do you want to do this?

[cdmafra]

Why do you want to do this?

Well, I don't explained the situation correctly.

I have a GUID field, what I need is to auto generate the GUID for all table entries already existing. And after that, to show GUID instead ID in the URL...

[Psycho]

Do you mean to say that you have a table with a primary key and a GUID key field and you want to replace instances of the Primary key used as foreign keys in other tables with the GUID?

 

If so, it is as simple as running an UPDATE query with a JOIN for each of those tables. Example:

 

UPDATE related_table
JOIN primary_table ON related_table.foreign_key_name = primary_table.primary_key_name
SET related_table.foreign_key_name = primary_table.guid

[cdmafra]

 

Do you mean to say that you have a table with a primary key and a GUID key field and you want to replace instances of the Primary key used as foreign keys in other tables with the GUID?

 

If so, it is as simple as running an UPDATE query with a JOIN for each of those tables. Example:

UPDATE related_table
JOIN primary_table ON related_table.foreign_key_name = primary_table.primary_key_name
SET related_table.foreign_key_name = primary_table.guid

 

No, no. I have just one table, and I need to keep ID and create the GUID (at this moment I already have the structure done). Now, I need to update automatically the GUID to all existing table records. And after, put the link working with GUID instead ID (example: www.mysite.com/?id=103  to www.mysite.com/?=guid=1fa16860-7e6f-468d-9450-65a33d6eabaf). But feeding PHP through GUID, there is an error of non-existing variable. As soon as possible I post my code here.

[Ch0cu3r]

If the GUID is already in the database, then you just got to change the part in your PHP code that fetches/receives the id and change it to guid.

 

For example you'll have code like this to output links

echo '<a href="site.com/?id=' . $row['id'] . '">link text</a>';

needs to be changed to

echo '<a href="site.com/?guid=' . $row['guid'] . '">link text</a>';

Any references to $_GET['id'] needs to be changed to $_GET['guid'].

 

And change your queries to fetch records that matches the guid and not id, eg

SELECT * FROM your_table WHERE guid = $guid

All you're doing is renaming variables

[Psycho]

To add the GUIDs run this

UPDATE table_name
SET GUID_Field = UUID()
WHERE GUID_Field = '' OR GUID_Field IS NULL

Replace "table_name" and "GUID_field" with the appropriate names.

 

As for updating the links, as Ch0cu3r stated, you need to find the code that creates those links and change it. It's impossible for us to tell you how to do that since we don't know anything about the code as you have failed to provide any. Most likely you will need to find the SELECT query that is used to get the data and ensure the GUID field is included in the SELECT parameters. Then find the code that produces the links and change them to use that value as well.

Edited October 4, 2013 by Psycho

[jazzman1]

Before doing anything, backup this table

[cdmafra]

If the GUID is already in the database, then you just got to change the part in your PHP code that fetches/receives the id and change it to guid.

 

For example you'll have code like this to output links

echo '<a href="site.com/?id=' . $row['id'] . '">link text</a>';

needs to be changed to

echo '<a href="site.com/?guid=' . $row['guid'] . '">link text</a>';

Any references to $_GET['id'] needs to be changed to $_GET['guid'].

 

And change your queries to fetch records that matches the guid and not id, eg

SELECT * FROM your_table WHERE guid = $guid

All you're doing is renaming variables

 

 

It was what I did, but the browser returns the error:

SCREAM: Error suppression ignored for Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\mrn_site\news_principais.php on line 10Call Stack#TimeMemoryFunctionLocation10.0015163408{main}( )..\index.php:021.0202193616include( 'C:\wamp\www\mrn_site\news_principais.php' )..\index.php:195  31.0222190760mysql_fetch_array ( )..\news_principais.php:10

 

 news_principais.php:10:

 <?php
if (isset($_REQUEST["guid"]))
{
$query = "SELECT * FROM news WHERE news_guid=".$_REQUEST["guid"]."";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
?>

index:195

    default:
      include("news_principais.php"); 
    break;
  }

[kicken]

GUID values have to be quoted in the query like strings. You'll also want to run it through mysql_real_escape_string before using it in your query.

[cdmafra]

GUID values have to be quoted in the query like strings. You'll also want to run it through mysql_real_escape_string before using it in your query.

Thank you. But How can I quote GUID values?

 

<?php
{
$query = "SELECT * FROM news WHERE destaque='Sim' and publicado='sim' ORDER BY news_id DESC LIMIT 4";
$result = mysql_query($query);


while ($row = mysql_fetch_array($result))


{
echo "<div class='three columns'>";
echo "<a href='?guid=".$row["news_guid"]." class='div-link'>";
  echo "<div class='desc-new'>";
  //imagem
  echo "<div class='img-block'><img src='".$row['news_image']."' title='".$row["news_title"]."' alt='".$row["news_title"]."'/></div>";
  //texto
  echo "<h4 class='new-title' onmouseover='none'>".$row["news_title"]."</h4>";
echo "<h4 class='new-subtitle' onmouseover='none'>".$row["news_subtitle"]."</h4>";
echo "<aside><p>".$row["news_desc"]."</p><p class='datapeq'>".$row["news_date"].", ".$row["hour"]."</p></aside>";
  echo "</div>";
echo "</a>";
echo "</div>";
}


}
?>

Edited October 5, 2013 by cdmafra

[Ch0cu3r]

Thank you. But How can I quote GUID values?

kicken meant in your SQL query

<?php 
if (isset($_REQUEST["guid"]))
{
    $guid = mysql_real_escape_string($_REQUEST["guid"]); // sanitize the guid 
    $query = "SELECT * FROM news WHERE news_guid='".$guid."'"; 
    $result = mysql_query($query); $row = mysql_fetch_array($result); ?>

Edited October 5, 2013 by Ch0cu3r

[cdmafra]

kicken meant in your SQL query

<?php 
if (isset($_REQUEST["guid"]))
{
    $guid = mysql_real_escape_string($_REQUEST["guid"]); // sanitize the guid 
    $query = "SELECT * FROM news WHERE news_guid='".$guid."'"; 
    $result = mysql_query($query); $row = mysql_fetch_array($result); ?>

Thank you very much!