Complicated mysql database retrieval

[mattm591]

Hi.
I'm writing a script which accesses a MySQL database and outputs information. To understand the problem you'll need a bit of information about how the system should work.

A user can add a publication to the database. This contains information on authors (multiple) as well as information on the publication such as the year it was made, who published it etc.

The user can set up a "style" which allows them to choose an order for the data to be outputted. So they can make one which first outputs authors surnames then the publication name. Or one which lets them output the Year, Title then authors surnames and first names.

I then have an output page which will use a style they've made as well as however many publications they've chosen and output all the publications in the order set by the style.

I am now writing the query to make this output, but am running into some problems.

First it seems far too complicated to try and order it as the information is retrieved from the databased, so instead I'm getting all the information and adding it to one big variable "$output".

I have made it so that the information that is selected is outputted to the variable. It is only outputting the information they ask for, so that's working good.

My problem is with ordering it. I'm not sure how to do two things.

1) Order the data in the way it was chosen when they created the style from the new variable $output

2) Place selected formatting they have chosen (such as a space, ", - etc.) between each piece of data in the output.

So effectivly I want a script which could turn

"HumphreysGeorgeThe Publication2007"

into

"George, Humphreys - The Publication (2007)"

I am not sure what I need to do to get this to work as my PHP knowledge is still fairly limited and it's hurting my head trying to get it working.

If someone could help me I would be incredibly grateful! I hope I have made the problem clear, it is quite tricky to explain exactly what I mean.

I will include the code I have made so far on the output (a few bits like connecting to the database and some parameters which are set are on a previous page but I don't think their necessary to include):

<?
//Query selected style
$stylequery=mysql_query("SELECT * FROM style WHERE styleid='$usestyle'");

//Retrieve information on selected style
$styledata=mysql_fetch_array($stylequery);

//Find number of fields in style
$fields=$styledata["fields"];

//Set a counter to loook through each field with
$counter=0;

//Start a loop to check each field requested
while($counter<$fields){

//Increase counter to move through loop
$counter++;

//Set each field
$field[$counter]=$styledata["field".$counter];

}

//Search through fields to find out if any author information is required
if(in_array("fa",$field,true)){

//Set a variable to state that a field is required
$title=true;
}

if(in_array("fb",$field,true)){

//Set a variable to state that a field is required
$year=true;
}

if(in_array("fc",$field,true)){

//Set a variable to state that a field is required
$surname=true;
}

if(in_array("fd",$field,true)){

//Set a variable to state that a field is required
$name=true;
}

if(in_array("fe",$field,true)){

//Set a variable to state that a field is required
$inital=true;
}

//Loop for each document
foreach($array as $docid){

//Select the authorids which match the current docid
$authidquery=mysql_query("SELECT authorid FROM authorship WHERE docid='$docid'");

//Count how many results were returned
$authidtotal=mysql_num_rows($authidquery);

//Set a counter to move through the rows with
$counter=0;

//Start a second loop to go through all of the author information
while($counter<$authidtotal){

//Get the current authid
$authid=mysql_result($authidquery,$counter);

//Run query to select information
$doquery=mysql_query("SELECT * FROM authors WHERE authorid='$authid'");

$get=mysql_fetch_array($doquery);

if($surname=="true"){
$output .= $get["surname"];
}
if($name=="true"){
$output .= $get["name"];
}
if($inital=+"true"){
$output .= $get["inital"];
}

//Clear the query for the next result
$query="";

//Increase the counter to move onto the next result
$counter++;

//Close authid loop
}

//Get the publication information requested

$docquery=mysql_query("SELECT * FROM docs WHERE docid='$docid'");

$docresult=mysql_fetch_array($docquery);

if($title=="true"){
$output .= $docresult["title"];
}

if($year=="true"){
$output .= $docresult["year"];
}

//Close docid loop
}

echo $output;
?>

Anyone who has the time to help me, it would be very much appreciated.

Thanks a lot!

Matt

[leatherback]

Hi,

Looks to me that what you have in 40 lines, could be done in 7. And with 1 or 2 queries instead of the nested multiple queries you run here.

Please le me know how styles and books or authors are linked (I see you use $array for your books, but I cannot see where that is created). If you let me know the:

Table names
Table ID colum names
Relation between the tables (e.g., authors.authorid = authorship.authorid)

I will have a stab at reordering this for you. After that we can have a look at the styles, which I presume will become somewhat easier to tackle.

[mattm591]

Thanks a lot for the reply. I had wondered if there was a simpler way to do what I was doing, but I didn't know of one 🙁

I'll include the code for the page prior to this one which lets the user define their parameters to create the list. There are other pages used to create styles and add publications, if you need to see them as well let me know.

<?
//list.php

//Disable server error message


//Connect to the database
if(@!include("db_connect.php")){

//If there was a problem doing this

//Set up error information
$errortype="simple";
$errortext="The script could not find the needed file to connect to the database";
$queryerror="'db_connect.php' missing from 'list.php'";
$fixerror="Ensure you have all required files in place";

//Include page to output error information
if(@!include("error.php")){
die("Fatal error: missing file 'error.php'");
}

//Stop calling script
die();

}
//Finished connecting to the database


//Feed in variables that may have already been set
$dowhat=$_POST['submit'];
$order=$_POST['order'];
$usestyle=$_POST['styles'];
$type=$_GET['type'];

if($type==0){
$type=-1;
}

//Get the text from the filter box and remove any whitespace from the front and end of it
$filter=trim($_POST['filter']);

//Create an array which contains all the publications selected
$array = array_merge(
    array_values($_POST['publications']),
    array_values($_POST['inarray'])); 

//Ensure that all items in the array are unique and the same document hasn't been submitted multiple times
$array = array_unique($array);

//If the "Go" button has been selected
if($dowhat=="Go"){

//Include the page to process the list (not yet avaliable)
include("dolist.php");

die();

}

//If the "Remove" button has been selected
if ($dowhat=="Remove"){

//Find out which publications were selected to be removed
if(isset($_POST['addedpublications'])){


//Take those publications out of the array
$array = array_diff($array, $_POST['addedpublications']); 

}
}

?>
<html>
<head>
<title>Publications Database - Create List</title>
<link href="main.css" rel="stylesheet" type="text/css">
<script type="text/JavaScript">
<!--
function MM_jumpMenu(targ,selObj,restore){ //v3.0
  eval(targ+".location='"+selObj.options[selObj.selectedIndex].value+"'");
  if (restore) selObj.selectedIndex=0;
}
//-->
</script>
</head>

<body class="bodystyle">
<h1 class="xbig">Publications Database - Create List</h1>
<h2 class="medium">Create a list of publications to export using this form. If you get confused about how to use it why not consult the <a href="listhelp.php">help</a>.</h2>
<br>
<form action="list.php?type=<? echo $type ?>" method="post">

<?

//If the array has been made
if(isset($array)){

//Select each item in the array
foreach ($array as $a){

//Create a hidden form value to send the array to the page if it's called again or when it's submitted
echo '<input name="inarray[]" type="hidden" value="' . $a . '">';

}
}

?>

<div align="left">
1) To start please select the type of publications you want:&nbsp;
<select name="type" onChange="MM_jumpMenu('parent',this,0)">
  <option value="list.php">Select Type</option>
  <option value="list.php?type=1" <? if($type=="1"){ echo "selected"; } ?>>Paper</option>
  <option value="list.php?type=2" <? if($type=="2"){ echo "selected"; } ?>>Conference Proceeding</option>
  <option value="list.php?type=3" <? if($type=="3"){ echo "selected"; } ?>>Full Book</option>
  <option value="list.php?type=4" <? if($type=="4"){ echo "selected"; } ?>>Chapter in Book</option>
  <option value="list.php?type=5" <? if($type=="5"){ echo "selected"; } ?>>Patent</option>
</select>
<br><br><br>
2) Now please select the style to apply to this data:&nbsp;
<select name="styles">
<?

//Get all the styles from the database which apply to the type selected
$stylequery=mysql_query("SELECT * FROM style WHERE type='$type'");

//Fetch the name of the style from the database
while ($stylename = mysql_fetch_array($stylequery)){

//Output the name of the style as an option in the select box
if ($usestyle==$stylename["styleid"]){
echo '<option value ="' . $stylename["styleid"] . '" selected>' . $stylename["stylename"] . '</option>';
} else {
echo '<option value ="' . $stylename["styleid"] . '">' . $stylename["stylename"] . '</option>';
}
}

?>
</select>
<br><br><br>
2) Now please select which fields to papers to include:
<br><br>
<input name="filter" type="text" value="<? echo $filter; ?>">
&nbsp;&nbsp;
<input name="submit" type="submit" value="Filter">
<br><br>
<table width="90%">
<tr>
<td>
<select name="publications[]" size="10" multiple>
<?

//If the "Filter" button has been selected
if($dowhat=="Filter"){

//Search the database for document names which match the filter word put in
$docquery=mysql_query("SELECT * FROM docs WHERE title LIKE '%$filter%' AND typeid='$type' order by title") or die("Fatal error: failed to run query on 'list.php'");
while ($docname = mysql_fetch_array($docquery)){
if(!in_array($docname["docid"], $array)){
echo '<option value="' . $docname["docid"] . '">' . substr($docname["title"],0,35) . ' (' . $docname["year"] . ')' .  '</option>';
}
}
} else {
$docquery=mysql_query("SELECT * FROM docs WHERE typeid='$type'");
while ($docname = mysql_fetch_array($docquery)){
if(!in_array($docname["docid"], $array)){
echo '<option value="' . $docname["docid"] . '">' . substr($docname["title"],0,35) . ' (' . $docname["year"] . ')' .  '</option>';
}
}
}
?>
</select>
<input name="submit" type="submit" value="Add">
</td>
<td>
<select name="addedpublications[]" size="10" multiple>
<?

if(isset($array)){

foreach($array as $a){

$docinfo=mysql_fetch_array(mysql_query("SELECT * FROM docs WHERE docid='$a'"),1);

echo '<option value="' . $docinfo["docid"] . '">' . substr($docinfo["title"],0,35) . ' (' . $docinfo["year"] . ')' .  '</option>';

}

}

?>
</select>

<input name="submit" type="submit" id="submit" value="Remove">
</td>
</tr>
</table>
<br><br>
3) Now choose how you would like your outputted list to be ordered:
<br><br>
Alphabetically (by title):&nbsp;<input name="order" type="radio" value="alphaname" <? if ($order=="alphaname"){ echo "checked"; } ?>>&nbsp;&nbsp;Alphabetically (by author):&nbsp;<input name="order" type="radio" value="alphaauth" <? if ($order=="alphaauth"){ echo "checked"; } ?>>&nbsp;&nbsp;Chronologically:&nbsp;<input name="order" type="radio" value="chron" <? if ($order=="chron"){ echo "checked"; } ?>>&nbsp;&nbsp;As selected:&nbsp;<input name="order" type="radio" value="as" <? if ($order=="as"){ echo "checked"; } ?>>
<br><br><br>
4) Ready to create your list?&nbsp;
<input name="submit" type="submit" id="submit" value="Go">
</div>
</form>
<?
include('footer.php');
?>
</body>
</html>

The table structure is like this.

authors
authorid
surname
name
inital

authorship
authorid
docid
typeid

docs
docid
title
year
typeid

type
typeid
type1
type2
type3
type4
type5

The type table is used to store extra variables on the particular publication.

There is also a style database, but that one is rather big as it has options for field 1 to 9, separators a to c with 1 to 9 for each of them and some other variables.

The styles table basically says which fields to include and what separators to put between them and then the other tables have the data.

If you need more clarification let me know.

Thanks a lot for your help! Really appreciated

[leatherback]

OK,

query, seems quite straightforward. Please correct me if I misunderstand:

// For now, select all colums in the tables starting with authorship
select * from authorship
// Link authorship to authors, using authorid in both table as linking colum
left join authors on authorship.authorid = authors.authorid
// ditto for docs
left join docs on authorship.docid = docs.docid
// ditto for types
left join types on authorship.typeid = types.typeid

$query = "select * from authorship
  left join authors on authorship.authorid = authors.authorid
  left join docs on authorship.docid = docs.docid
  left join types on authorship.typeid = types.typeid";

$all_records = mysql_query($query) or die(mysql_error());

But it was not that easy: You have $array which holds document IDs. I will assume for now these are just numbers. I would like to create a list of them, separated by commas, enclosed by brackets:

$doclist = implode(',', $array);
$doclist = "(".$doclist.")";

So we only want records that are present in the doclist:

$doclist = implode(',', $array);
$doclist = "(".$doclist.")";

$query = "select * from authorship
  left join authors on authorship.authorid = authors.authorid
  left join docs on authorship.docid = docs.docid
  left join types on authorship.typeid = types.typeid
  where docs.docid in ".$doclist"";

$all_records = mysql_query($query) or die(mysql_error());
echo "<pre>";
while($record = mysql_fetch_array($all_records))
  {
  print_r($record);
  }
echo "</pre>";

Let me know how this works for you, and we can have a look at the styling.

edit: Typo in the implode. Also: I realize that you might get a number of records for the same document. This is no problem, as we will deal with this issue in the next phase, when we pull out the styling. Meanwhile, I will be off to the kichten to make a quick dinner, and be back in 30 mins. Also edited some formatting & output for your debug-testrun

[mattm591]

I'm a little confused by what your doing with the left join bit as I've never used it before.

Anyway, I have modified it to change types to type (as the table is called that and it was giving an error) but now it says
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''(())'' at line 5"

which has completely thrown me as I don't understand what the SQL syntax is doing on line 5 😛 any ideas? And is the putting the . in between the names of tables joining them for the purpose of the script as if they were one?

[leatherback]

Could you please post the final query that is throwing the error? It is even better if you -before the mysql_query statment- would echo the $query, and post both the source code for the query, and the query which is passed to the database.

The join syntax is a way to basically 'merge' two tables in your database, and linking the records using a colum in each table to make sure the records match.

The . between the tablename and the columname is required since you use the same colum name in multiple tables: It identifies which table the specified colum name comes from.

[mattm591]

<?
$query = "select * from authorship
  left join authors on authorship.authorid = authors.authorid
  left join docs on authorship.docid = docs.docid
  left join type on authorship.typeid = type.typeid";
echo $query;
$all_records = mysql_query($query) or die(mysql_error()); 
$doclist - implode(',', $array);
$doclist = "(".$doclist.")"; 
$doclist - implode(',', $array);
$doclist = "(".$doclist.")";

$query = "select * from authorship
  left join authors on authorship.authorid = authors.authorid
  left join docs on authorship.docid = docs.docid
  left join type on authorship.typeid = type.typeid
  where docs.docid in '$doclist'";

$all_records = mysql_query($query) or die(mysql_error()); 
echo mysql_result($allrecords,0)
?>

The query being used that it reckons has an error is:
"select * from authorship left join authors on authorship.authorid = authors.authorid left join docs on authorship.docid = docs.docid left join type on authorship.typeid = type.typeid"

I wasn't sure what sort of output to use. I've only really ever used mysql_fetch_array to get specific pieces of data and mysql_result to get the data from a certain row. Also I didn't know how much other code I'd need to add back in until I could see what this code was doing (I'm not so good at working on code without seeing a visual output as I go) so obviously I can't see it yet as it's just giving an error.

[leatherback]

Could you try just running this:

$doclist = implode(',', $array);
$doclist = "(".$doclist.")";

$query = "select * from authorship
  left join authors on authorship.authorid = authors.authorid
  left join docs on authorship.docid = docs.docid
  left join type on authorship.typeid = type.typeid
  where docs.docid in ".$doclist;

$all_records = mysql_query($query) or die(mysql_error());
echo "<pre>";
while($record = mysql_fetch_array($all_records))
  {
  print_r($record);
  }
echo "</pre>"; 

and let me know what it gives?

[mattm591]

Certainly,

Array
(
[0] => 1
[authorid] => 1
[1] => 1
[docid] => 1
[2] => 1
[typeid] => 1
[3] => 1
[4] => McDonald
[surname] => McDonald
[5] => Matt
[name] => Matt
[6] => S
[inital] => S
[7] => 1
[8] => The Confence Notes
[title] => The Confence Notes
[9] => 2004
[year] => 2004
[10] => 2
[11] => 1
[12] => The Conference
[type1] => The Conference
[13] => St. Petersburg
[type2] => St. Petersburg
[14] => The Notes
[type3] => The Notes
[15] => PTA
[type4] => PTA
[16] =>
[type5] =>
)
Array
(
[0] => 2
[authorid] => 2
[1] => 1
[docid] => 1
[2] => 1
[typeid] => 1
[3] => 2
[4] => McCarthy
[surname] => McCarthy
[5] => Clive
[name] => Clive
[6] => O
[inital] => O
[7] => 1
[8] => The Confence Notes
[title] => The Confence Notes
[9] => 2004
[year] => 2004
[10] => 2
[11] => 1
[12] => The Conference
[type1] => The Conference
[13] => St. Petersburg
[type2] => St. Petersburg
[14] => The Notes
[type3] => The Notes
[15] => PTA
[type4] => PTA
[16] =>
[type5] =>
)
Array
(
[0] => 3
[authorid] => 3
[1] => 2
[docid] => 2
[2] => 2
[typeid] => 2
[3] => 3
[4] => Humphreys
[surname] => Humphreys
[5] => Craig
[name] => Craig
[6] => J
[inital] => J
[7] => 2
[8] => The Notes on Potatoes
[title] => The Notes on Potatoes
[9] => 1901
[year] => 1901
[10] => 2
[11] => 2
[12] => Potatoes
[type1] => Potatoes
[13] => Germany
[type2] => Germany
[14] => The Notes Take 2
[type3] => The Notes Take 2
[15] => McCafee
[type4] => McCafee
[16] =>
[type5] =>
)
Array
(
[0] => 4
[authorid] => 4
[1] => 3
[docid] => 3
[2] => 3
[typeid] => 3
[3] => 4
[4] => test
[surname] => test
[5] => test
[name] => test
[6] => r
[inital] => r
[7] => 3
[8] => OfficeNotes
[title] => OfficeNotes
[9] => 2001
[year] => 2001
[10] => 2
[11] => 3
[12] => Office Information
[type1] => Office Information
[13] => Nachotown
[type2] => Nachotown
[14] => CasanovasNotes
[type3] => CasanovasNotes
[15] => WTL
[type4] => WTL
[16] =>
[type5] =>
)
Array
(
[0] => 5
[authorid] => 5
[1] => 3
[docid] => 3
[2] => 3
[typeid] => 3
[3] => 5
[4] => Michaels
[surname] => Michaels
[5] => George
[name] => George
[6] => H
[inital] => H
[7] => 3
[8] => OfficeNotes
[title] => OfficeNotes
[9] => 2001
[year] => 2001
[10] => 2
[11] => 3
[12] => Office Information
[type1] => Office Information
[13] => Nachotown
[type2] => Nachotown
[14] => CasanovasNotes
[type3] => CasanovasNotes
[15] => WTL
[type4] => WTL
[16] =>
[type5] =>
)

This data is stuff I've been testing with. I think I may have added something manually so it might not have any relative links anywhere else in the structure. If this is a problem I can run it again with some better data.

[leatherback]

OK,
Cool. Looks like we are onto something here. As you hopefully noticed: We are now getting all the data from the datbase, based on the list of selected items you provided. Does it look to you like we are getting:
=> All relevant docs
=> All relevant people
=> All relevant styles?

Thank please explain the sort of output you are after. Or play ith this output yourself.

What you see as that the query gives you:
One record per doc, per author, per style.

So you should expand the query possibly with:

order by docid, authorid, typeid

to make sure the records are ordered in a useful way.

[mattm591]

The problem is the order would change each time, because there would be different styles.

Once it's in that order the documents themselves needed to be ordered alphabetically, or by year or as they're added into the array.

Another problem I was having earlier was that the authors need to be output in different orders. So for example they could be done by name then surname or surname then inital or name and inital but the problem is you don't want to list all the surnames and then all the names, you need surname,name surname,name etc.

So there are a lot of variables, including whether or not something bold and up to 3 different formatting styles that need to be between each field and that's all going way over my head.

The way you got that other output seems to be getting there, but I don't now know how I'd order that, or how to get the formatting in between.

If you have the time to look at those two pages and help me some more in getting this output to display properly I would certainly appreciate it 🙂
Thanks.

[leatherback]

Hi Matt,

sorting:

You can have a sort-string before the output sorting:

$query = "select * from authorship
  left join authors on authorship.authorid = authors.authorid
  left join docs on authorship.docid = docs.docid
  left join type on authorship.typeid = type.typeid
  where docs.docid in ".$doclist"
  order by TITLE, authorid, typeid";

As you noticed: It may output several records per document. You want to have these lumped together, for sorting. I am not completely sure what all the options are that come in. Something like this takes a lot of time to tweak.

But.. You could fill an array with all the values quite easily, and use that for spitting things out. First you create an empty templat ewith all the options that might be stored. Then you start runnin through the records and strore them:

$currentdoc = -1;
while($row = mysql_fetch_array($result))
  {
  // create a new empty array:
  if($correntdoc <> $row[docid])
    {
    $outdoc[$row['docid']] = array('title', 'type', 'authors'=>array())
    }
  $outdoc[$row['docid']]['title'] = $row['title'];
  $outdoc[$row['docid']]['type'] = $row['type'];
  $outdoc[$row['docid']][$row['author']] = $row['author'];
  }

[mattm591]

Ok thanks for this. I don't have time now, but I'll work on this Thursday and let you know how I get on. I'll probably run into trouble somewhere though 😛

Thanks for all your help it's very much appreciated!

[leatherback]

Matt: no problem. I am flying to the Netherlands tomorrow for a series of meetings at some uni's there. Might be back on sat/sunday.

Drop me a PM if you would be interested in developing this further together..