Jump to content

SQL newbie help

hoponhiggo

By hoponhiggo
in PHP Coding Help

 Share

Recommended Posts

hoponhiggo Member

hoponhiggo

Posted
Posted

Hi guys - i have some code which echos an image from a directory based on the user who is logged in...

 

<?php
//to display image from source
$dir = "profpics";

$sql = "SELECT prof_pic FROM users WHERE username = '{$_SESSION['MM_Username']}'";
$res = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($res) == 0) die("Username not found in database.");

$row = mysql_fetch_array($res);
echo "<img src='$dir/{$row['prof_pic']}' width='38' height='38'><br>";
?>

 

I have some other code which allows users to post a message on my 'wall'

 

The message is saved in my database in a 'messages' table with 3 columns: messageid, messgae, userid.

 

The userid column is the same in my messages table as in my users table where the image is echo'd from.

 

Can anybody help me write some code which will allow me to echo the images based on who created the message instead of who is logged in?

 

Thanks in advance

 

 

Link to comment
Share on other sites
hoponhiggo Member

hoponhiggo

Posted
  • Author
Posted

Thanks for your help guys

 

Until i figure out how to link the two tables using the userid (i have tried and failed before) ill continue to use the username i think...

 

I have changed the code to the following:

 

<?php
//to display image from source
$dir = "profpics";

$sql = "SELECT prof_pic FROM users LEFT JOIN messages on (users.username = messages.username)";

$res = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($res) == 0) die("Username not found in database.");

$row = mysql_fetch_array($res);
echo "<img src='$dir/{$row['prof_pic']}' width='38' height='38'><br>";
?>

 

But it is still only showing the prof pic of the user that is logged in, not the user that left the message. Any suggestions?

Link to comment
Share on other sites
Muddy_Funster Regular Member

Muddy_Funster

Posted
Posted

We will need the schema of your images table, right?

+1

 

Post up a full breakdown of your tables and we can help you much better.  You said in your first post that there were 3 columns in your message table and then have code latter on linking to a cloumn that was not listed as being in there.

 

we need to know what you are storing in the tables to tell you how to retrieve it propperly.

Link to comment
Share on other sites
Muddy_Funster Regular Member

Muddy_Funster

Posted
Posted

I assume that comments.msg_id_fk = meassages.id_fk - and allso that the use of _fk is reffering to Foreign Key

 

What are messages.id_fk and a comments.id_fk actualy refferencing and how are they populated?

 

Also, you shouldn't really have username in the messages table, and if you do it should really be the same wordlength as it is in the users table.

Link to comment
Share on other sites
hoponhiggo Member

hoponhiggo

Posted
  • Author
Posted
I assume that comments.msg_id_fk = meassages.id_fk - and allso that the use of _fk is reffering to Foreign Key

 

Yes - u assume correctly. Also, it should be said that the comments & messages tables are part of a 'wall posting' script which was written by somebody else and i have been adapting to meet my needs, along with all the php that goes with it.

 

Also, you shouldn't really have username in the messages table, and if you do it should really be the same wordlength as it is in the users table.

 

I did try to create a variable using the 'userid' of the session user (the user that is logged in) that but this didnt work. The only way i could link the message and the user posting the message was by using the $_SESSION['MM_Username'] variable

Link to comment
Share on other sites
hoponhiggo Member

hoponhiggo

Posted
  • Author
Posted

you missed out the important one :

 

What are messages.id_fk and a comments.id_fk actualy refferencing and how are they populated?

;)

 

I think they are an auto-incrementing ID of a message that has been posted - thats all i can fathom!

 

Any sugestions to get me in the right direction would be greatly appreciated. Im really struggling here

 

Thanks

Link to comment
Share on other sites
Muddy_Funster Regular Member

Muddy_Funster

Posted
Posted

But there is no field for username in the comments table... :confused:

 

Do you have a $_SESSION variable that has the user id in it?

 

With what you are using you could get what you want for the message pic, but not the comment.

 SELECT meassages.message, users.username, users.prof_image FROM users RIGHT JOIN messages ON users.username = messages.username

 

But with the tables the way they are it's not going to do much more than that.

 

If you have the time we might be better off starting from the ground up rather than patching together a fix.

Link to comment
Share on other sites
hoponhiggo Member

hoponhiggo

Posted
  • Author
Posted
But there is no field for username in the comments table... :confused:

 

Thats becouse im trying to make it work for the messages first - i was then going to appy the same code to comments!

 

Do you have a $_SESSION variable that has the user id in it?

 

No - i tried to make one, but it didnt seem to work correctly!

 

If you have the time we might be better off starting from the ground up rather than patching together a fix.

 

Time i have. Knowledge - not so much!!

 

Link to comment
Share on other sites
hoponhiggo Member

hoponhiggo

Posted
  • Author
Posted 
<?php require_once('Connections/pwnedbook.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}
?>
<?php
// *** Validate request to login to this site.
if (!isset($_SESSION)) {
  session_start();
}

$loginFormAction = $_SERVER['PHP_SELF'];
if (isset($_GET['accesscheck'])) {
  $_SESSION['PrevUrl'] = $_GET['accesscheck'];
}

if (isset($_POST['Username'])) {
  $loginUsername=$_POST['Username'];
  $password=$_POST['Password'];
  $MM_fldUserAuthorization = "";
  $MM_redirectLoginSuccess = "index.php";
  $MM_redirectLoginFailed = "Register.php";
  $MM_redirecttoReferrer = false;
  mysql_select_db($database_pwnedbook, $pwnedbook);
  
  $LoginRS__query=sprintf("SELECT username, password FROM users WHERE username=%s AND password=%s",
    GetSQLValueString($loginUsername, "text"), GetSQLValueString($password, "text")); 
   
  $LoginRS = mysql_query($LoginRS__query, $pwnedbook) or die(mysql_error());
  $loginFoundUser = mysql_num_rows($LoginRS);
  if ($loginFoundUser) {
     $loginStrGroup = "";
    
if (PHP_VERSION >= 5.1) {session_regenerate_id(true);} else {session_regenerate_id();}
    //declare two session variables and assign them
    $_SESSION['MM_Username'] = $loginUsername;
    $_SESSION['MM_UserGroup'] = $loginStrGroup;	      

    if (isset($_SESSION['PrevUrl']) && false) {
      $MM_redirectLoginSuccess = $_SESSION['PrevUrl'];	
    }
    header("Location: " . $MM_redirectLoginSuccess );
  }
  else {
    header("Location: ". $MM_redirectLoginFailed );
  }
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Log In</title>
<style type="text/css">
body,td,th {
color: #FFF;
font-family: "Comic Sans MS", cursive;
}
body {
background-color: #000;
}
</style>
<link href="Css/estilo.css" rel="stylesheet" type="text/css" />
</head>

<body>
<div align="center"><a href="index.php"><img src="Images/logon_logo.gif" width="300" height="150" /></a>
</div>
<p align="center">Welcome to Pwnedbook.co.uk - the unsocial network for gamers!  </p>
<p align="center">You need to log in to see this site. </p>
<form id="form1" name="form1" method="POST" action="<?php echo $loginFormAction; ?>">
  <p align="center">
    <label for="Username">Username</label>
    <input type="text" name="Username" id="Username" />
  </p>
  <p align="center">
    <label for="Password">Password</label>
    <input type="password" name="Password" id="Password" />
  </p>
  <p align="center">
    <input type="submit" name="Submit" id="Submit" value="Go" />
  </p>
</form>
</body>
</html>

Link to comment
Share on other sites
hoponhiggo Member

hoponhiggo

Posted
  • Author
Posted

i think its these your after:

 

insert message

 

<?php

//initialize the session
if (!isset($_SESSION)) {
  session_start();
}

//define database
include("db.php");

// if content has been sent
if(isset($_POST["textarea_noticia"])){
	$msg = $_POST["textarea_noticia"];
		$username = $_SESSION['MM_Username'];

	// Insert information
	$sql = mysql_query("INSERT INTO messages(message,username)values('$msg','$username')");
	$result = mysql_query("SELECT * FROM messages order by msg_id desc");
	$row = mysql_fetch_assoc($result);
	$id = $row["msg_id"];
	$msg = $row["message"];
}
?>
<div class="bar<?php echo $id;?>">
<div class="post_box">
	<?php
//to display image from source
$dir = "profpics/";

//opening directory
if ($opendir = opendir($dir))
{
//reading directory
while (($file = readdir($opendir)) !== FALSE)
{
	if ($file!="."&&$file!="..")
	echo "<img src = '$dir/$file' img width='38' img height='38'><br>";
}
}

?>
	<div class="noticia"><?php echo $msg; ?></div>
	<div class="eliminar_noticia"><a href="javaScript:void(0)" id="<?php echo $id;?>" class="eliminar_noticia_actualizado">Del</a></div>
	<div class="enlace_comentar"><a href="javaScript:void(0)" id="<?php echo $id;?>" class="comentar">Comment</a></div>

	<div id="contenedor_textarea_comentario" class="contenedor_textarea_comentario<?php echo $id;?>">
		<div class="comentario_caja" id="comentario_caja<?php echo $id;?>">
			<form name="<?php echo $id;?>" method="post">
				<textarea class="text_area" name="comentario_valor" id="textarea<?php echo $id;?>"></textarea>
				<br />
				<input type="submit" value="Enter" class="enviar_comentario" id="<?php echo $id;?>" />
			</form>
		</div>
		<!-- Div donde se añadiran los comentarios -->
		<div id="comentario_cargado<?php echo $id;?>"></div>
	</div>
</div>
</div>

 

Insert comment

 

<?php
include("db.php");
if(isSet($_POST['comentario_valor'])){
	$id=time();// Demo Use
	$comment=$_POST['comentario_valor'];
	$id=$_POST['id'];

	$sql=mysql_query("insert into comments(comment,msg_id_fk)values('$comment','$id')");
	$result=mysql_query("select * from comments order by com_id desc");
	$row=mysql_fetch_array($result);
	$com_id=$row['com_id'];
	$comment=$row['comment'];	
}
?>
<div class="conetenedor_comentarios" id="conetenedor_comentarios<?php echo $com_id;?>">
<img src="Img/comment.png" width="38" height="38" class="imagen" />
<div class="mostrar_comentario" id="mostrar_comentario<?php echo $com_id;?>"><?php echo $comment;?></div>
<div class="comentario_borrar"><a href="#" id="<?php echo $com_id; ?>" class="comentario_borrar_actualizar">X</a></div>	
<div class="enlace_comentar"></div>
</div>

 

 

or it may be these:

 

messages

<?php
include("db.php");
if($_POST['msg_id']){
	$id=$_POST['msg_id'];
	$id = mysql_escape_String($id);
	$sql = mysql_query("delete from messages where msg_id='$id'");
	$sql1 =  mysql_query("delete from comments where msg_id_fk='$id'");
	if($sql && $sql1)
		return true;
	else
		return false;
}
?>

 

comments:

<?php
include("db.php");
if($_POST['com_id'])	{
	$id=$_POST['com_id'];
	$id = mysql_escape_String($id);
	$sql = "delete from comments where com_id='$id'";
	mysql_query( $sql);
}
?>

Link to comment
Share on other sites
Muddy_Funster Regular Member

Muddy_Funster

Posted
Posted

ok, here's what I got for you.  These should replace your existing pages, but I havn't tested any of this, so you will NEED to make backups of the ones you have.  It's the best I could think of just now to make it work the way I think you want it to.  Let me know how it goes.

 

login:

<?php require_once('Connections/pwnedbook.php'); 
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}
?>
<?php
// *** Validate request to login to this site.
if (!isset($_SESSION)) {
  session_start();
}

$loginFormAction = $_SERVER['PHP_SELF'];
if (isset($_GET['accesscheck'])) {
  $_SESSION['PrevUrl'] = $_GET['accesscheck'];
}

if (isset($_POST['Username'])) {
  $loginUsername=$_POST['Username'];
  $password=$_POST['Password'];
  $MM_fldUserAuthorization = "";
  $MM_redirectLoginSuccess = "index.php";
  $MM_redirectLoginFailed = "Register.php";
  $MM_redirecttoReferrer = false;
  mysql_select_db($database_pwnedbook, $pwnedbook);
  
  $LoginRS__query=sprintf("SELECT username, password, id FROM users WHERE username=%s AND password=%s",
    GetSQLValueString($loginUsername, "text"), GetSQLValueString($password, "text")); 
   
  $LoginRS = mysql_query($LoginRS__query, $pwnedbook) or die(mysql_error());
  $loginFoundUser = mysql_num_rows($LoginRS);
  if ($loginFoundUser) {
     $loginStrGroup = "";
     $pullID = mysql_fetch_assoc($LoginRS);
     $usrID = $pullID['id'];
    
if (PHP_VERSION >= 5.1) {session_regenerate_id(true);} else {session_regenerate_id();}
    //declare two session variables and assign them
    $_SESSION['MM_Username'] = $loginUsername;
    $_SESSION['MM_UserGroup'] = $loginStrGroup;
    $_SESSION['MM_userid'] = $usrID	      

    if (isset($_SESSION['PrevUrl']) && false) {
      $MM_redirectLoginSuccess = $_SESSION['PrevUrl'];	
    }
    header("Location: " . $MM_redirectLoginSuccess );
  }
  else {
    header("Location: ". $MM_redirectLoginFailed );
  }
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Log In</title>
<style type="text/css">
body,td,th {
color: #FFF;
font-family: "Comic Sans MS", cursive;
}
body {
background-color: #000;
}
</style>
<link href="Css/estilo.css" rel="stylesheet" type="text/css" />
</head>

<body>
<div align="center"><a href="index.php"><img src="Images/logon_logo.gif" width="300" height="150" /></a>
</div>
<p align="center">Welcome to Pwnedbook.co.uk - the unsocial network for gamers!  </p>
<p align="center">You need to log in to see this site. </p>
<form id="form1" name="form1" method="POST" action="<?php echo $loginFormAction; ?>">
  <p align="center">
    <label for="Username">Username</label>
    <input type="text" name="Username" id="Username" />
  </p>
  <p align="center">
    <label for="Password">Password</label>
    <input type="password" name="Password" id="Password" />
  </p>
  <p align="center">
    <input type="submit" name="Submit" id="Submit" value="Go" />
  </p>
</form>
</body>
</html>

 

message submit:

!-- Post a message

<?php

//initialize the session
if (!isset($_SESSION)) {
  session_start();
}

//define database
include("db.php");

// if content has been sent
if(isset($_POST["textarea_noticia"])){
	$msg = $_POST["textarea_noticia"];
		$userid = $_SESSION['MM_userid'];

	// Insert information
	$sql = mysql_query("INSERT INTO messages(message, id_fk)values('$msg', $userid)") or die (mysql_error());
	$qry_retrn = "SELECT mesg_id, message, user_name, prof_pic FROM messages LEFT JOIN users ON (messages.id_fk = users.id) order by msg_id desc";
    $result = mysql_query($qry_retrn) or die (mysql_error());
	$row = mysql_fetch_assoc($result);
	$id = $row["msg_id"];
	$msg = $row["message"];
	$usr = $row['username']; //will need to be added to display
	$pic = $row['prof_pic']; //will need to be added to display
	}
?>
<div class="bar<?php echo $id;?>">
<div class="post_box">
	<?php
//to display image from source
$dir = "profpics/";

//opening directory
if ($opendir = opendir($dir))
{
//reading directory
while (($file = readdir($opendir)) !== FALSE)
{
	if ($file!="."&&$file!="..")
	echo "<img src = '$dir/$file' img width='38' img height='38'><br>";
}
}

?>
	<div class="noticia"><?php echo $msg; ?></div>
	<div class="eliminar_noticia"><a href="javaScript:void(0)" id="<?php echo $id;?>" class="eliminar_noticia_actualizado">Del</a></div>
	<div class="enlace_comentar"><a href="javaScript:void(0)" id="<?php echo $id;?>" class="comentar">Comment</a></div>

	<div id="contenedor_textarea_comentario" class="contenedor_textarea_comentario<?php echo $id;?>">
		<div class="comentario_caja" id="comentario_caja<?php echo $id;?>">
			<form name="<?php echo $id;?>" method="post">
				<textarea class="text_area" name="comentario_valor" id="textarea<?php echo $id;?>"></textarea>
				<br />
				<input type="submit" value="Enter" class="enviar_comentario" id="<?php echo $id;?>" />
			</form>
		</div>
		<!-- Div donde se añadiran los comentarios -->
		<div id="comentario_cargado<?php echo $id;?>"></div>
	</div>
</div>
</div>

 

comment submit:

<?php
include("db.php");
if(isSet($_POST['comentario_valor'])){
	$id=time();// Demo Use
	$comment=$_POST['comentario_valor'];
	$id=$_POST['id'];
	$user = $_SESSION['MM_userid'];

	$sql=mysql_query("insert into comments(comment, msg_id_fk, id_fk)values('$comment',$id, $user)") or die (mysql_error());

    $qry_back = "SELECT com_id, comment, ".
    "CASE WHEN comments.id_fk IS NULL THEN 'Annon' ELSE (SELECT user_name FROM users WHERE users.user_id = comments.id_fk) END AS postedBy, ".
    "CASE WHEN comments.id_fk IS NULL THEN 'http://domain/folder/guest.img' ELSE (SELECT prof_pic FROM users WHERE users.user_id = comments.id_fk) END AS com_image ".
    "FROM comments ORDER BY com_id desc";
    //you will need to change the 'http://domain/folder/guest.img' to what you will use as a default image for non members.
	$result=mysql_query($qry_back) or die (mysql_error());
    $row=mysql_fetch_array($result);
	$com_id=$row['com_id'];
	$comment=$row['comment'];
    $poster = $row['postedBy'];  //will need to be added to display
    $img = $row['com_image'];	   //will need to be added to display
}
?>
<div class="conetenedor_comentarios" id="conetenedor_comentarios<?php echo $com_id;?>">
<img src="Img/comment.png" width="38" height="38" class="imagen" />
<div class="mostrar_comentario" id="mostrar_comentario<?php echo $com_id;?>"><?php echo $comment;?></div>
<div class="comentario_borrar"><a href="#" id="<?php echo $com_id; ?>" class="comentario_borrar_actualizar">X</a></div>	
<div class="enlace_comentar"></div>
</div>

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.